How to nest IF & SUM formula?

March 15, 2016 at 16:43:32
Specs: Windows 7
I've got most of this to work ezcept how to get Y12 to realize a,b or c but isn't there an easier formula
Trying to figure out how to get this result
in cell aa12
Cells B5:S5 have a variable value
4 3 3 4 4 3 4 3 3 4 3 3 4 4 3 4 3 3
Cells B12:S12 have a changing value
6 3 4 5 5 4 5 4 5 5 4 10 10 10 10 10
Cells AD12:AU12 are what the difference is between b12:s12-b5:s5
2 0 1 1 1 1 1 1 2 1 1 7 6 6 7 6 7 7
Cell Y12 has a variable value
A,B or C

The goal is to place Result in cell aa12 and copy down/over
Result:
if Y12 value is A, SUM(B12-B5) and IF(AD12>2,AD12-2,IF(AD12<=2,AD12-AD12))
if Y12 value is B, SUM(B12-B5) and IF(AD12>3,AD12-3,IF(AD12<=3,AD12-AD12))
if Y12 value is C, SUM(B12-B5) and IF(AD12>4,AD12-4,IF(AD12<=4,AD12-AD12))
Any advice welcomed

message edited by Basecalc


See More: How to nest IF & SUM formula?

Report •

#1
March 16, 2016 at 06:27:00
I'm confused.

The last clause of all of your IF's reads: AD12-AD12

Since that will always resolve to 0 (zero) why are doing a calculation?

In addition, since you are checking for 3 different numbers, you would have to check for AD12>4 first (since e.g 5 would be greater than both 2 and 3 and you wouldn't get the result you want). However, if you check for AD12>4 and AD12 contains 3, the AD<=4 clause will be TRUE and the AD12-AD12 will be calculated and return 0.

In other words, you can't have both AD12>2 (or >3) return AD12-2 (or -3) and also have AD12<=4 return AD12-AD12 (0).

Finally, I'm not even sure what you are trying to SUM. Are you trying the to calculate B12 minus B5 plus the value returned from the Nested IF? Your parenthesis and the word "and" make it confusing. Does "and" mean "plus" in this case?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 16, 2016 at 08:54:06
The answer would be yes to your final comment. This may help to keep it simple, what formula should be used to tell if cell y12 has an A, B or C in it for it to know which range to search for the result
Hope this helps

message edited by Basecalc


Report •

#3
March 16, 2016 at 09:25:15
While all of the detail is appreciated, it still doesn't answer these 2 questions:

Why are you calculating AD12-AD12? The result will always be 0, so why not just use 0 in your formula?

Instead of this:

IF(AD12>2,AD12-2,IF(AD12<=2,AD12-AD12))

Why aren't you using this?

IF(AD12>2,AD12-2,IF(AD12<=2,0))

You also haven't addressed the issue that you are asking for 2 different results under the same set of circumstances.

Follow this logic:

# 1 - For the following Nested IF, you want to return AD12-2 if AD12>2

IF(AD12>2,AD12-2,IF(AD12<=2,AD12-AD12))

#2 - For the following Nested IF, you want to return 0 if AD12<=4:

IF(AD12>4,AD12-4,IF(AD12<=4,AD12-AD12)

OK, so let's say AD12 = 2.5.

#1 says return AD12-2 since AD12>2, yet #2 says return 0 since AD12<4.

You can't have it both ways.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 18, 2016 at 07:53:56
Actually yes I can have it both ways as I want it.
The answer to Instead of this:
Why I calculated IF(AD12>2,AD12-2,IF(AD12<=2,AD12-AD12)) gives me the correct result: the difference from what is over the minus 2, 3 or 4 etc or else the result is zero and as I mentioned in the beginning "I've got most of this to work ezcept how to get Y12 to realize a,b or c"
So I'l ask in a different way, what formula can I use in cell AA12 to choose the sum
of which range from ad..:au.. or the other based on if cell Y12 has an a or b or c in it
BC

message edited by Basecalc


Report •

#5
March 18, 2016 at 09:52:42
If you don't mind, I'm going to take this in pieces to avoid any further confusion.

I have asked this question twice and have yet to see an answer:

The last clause of all of your IF's reads: AD12-AD12

IF(AD12>2,AD12-2,IF(AD12<=2,AD12-AD12))

Since AD12-AD12 will always resolve to 0 (zero) why are you asking Excel to do that calculation?

In other words,why aren't you just placing a 0 in your formula since AD12-AD12 will always return 0?

IF(AD12>2,AD12-2,IF(AD12<=2,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 18, 2016 at 10:20:20
A couple of more observations.

Your post used the following: (Emphasis mine)

if Y12 value is A, SUM(B12-B5) and IF(AD12>2,AD12-2,IF(AD12<=2,AD12-AD12))
if Y12 value is B, SUM(B12-B5) and IF(AD12>3,AD12-3,IF(AD12<=3,AD12-AD12))
if Y12 value is C, SUM(B12-B5) and IF(AD12>4,AD12-4,IF(AD12<=4,AD12-AD12))

Which can be broken down to:
IF Y12 is A then you always calculate by 2
IF Y12 is B then you always calculate by 3
IF Y12 is C then you always calculate by 4

Why have the letters when you can just use the numbers?
Why not just replace the Letters in Y12 with the Numbers.

Also, in all three instances of A, B & C you sum the range B12:B5

Since we can convert the letters ABC, to the numbers 2,3 or 4
you can probably use a formula like:

=IF(AD12>Y12,AD12-Y12,IF(AD12<=Y12,AD12-AD12))+SUM(B5:B12)

or using DerbDad03 observation:

=IF(AD12>Y12,AD12-Y12,IF(AD12<=Y12,0))+SUM(B5:B12)

Is that way your looking for.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#7
March 18, 2016 at 19:43:43
I think this may be what you are looking for. If not, let us know what I missed.

=B12-B5+
IF(AND(Y12="A",AD12>2),AD12-2,
IF(AND(Y12="B",AD12>3),AD12-3,
IF(AND(Y12="C",AD12>4),AD12-4,0)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
March 18, 2016 at 20:19:11
I understand the protocol for not allowing direct communication, so be it, but at least you understand there's something being missed.
I made a mistake to not revise/correct the end result to the calc in which that created a stumbling block on your side but was defined in the original question but to clarify first.
IF Y12 Has A,B pr C then sum (determined range) so if it's A the range a4:b3. or B a3:f4, etc.

Report •

#9
March 18, 2016 at 20:37:32
OK, now you have absolutely lost me.

However, this time I mean it when I say that I will offer no more assistance until you answer my question about AD12-AD12. This is your third (and last) chance to explain to me why you aren't simply using 0.

Answer that question, or at least acknowledge that you will use 0 instead of AD12-AD12 and then we can get back to your bigger issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 18, 2016 at 20:56:47
Again I'll answer that question as I did Basecalc March 18, 2016 at 07:53:56, gives me the correct result: the difference from what is over/above the minus from the buffer of 2,3 or 4 which tells that no one did a mach-up with the original number I gave in the first question and with those results may have helped communications along with reading my answers, See ESC for golf as a comparison. So regardless of any other calculations it appears that your ways of thinking are limit if you continue to be stuck for an answer.

Report •

#11
March 18, 2016 at 21:37:39

Report •

Ask Question