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 CThe 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

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

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

While all of the detail is appreciated, it still doesn't answer these 2 questions: Why are you

calculatingAD12-AD12? The result willalwaysbe 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.

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

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-AD12Since AD12-AD12 will

resolve to 0 (zero) why are you asking Excel to do that calculation?alwaysIn other words,why aren't you just placing a

0in your formula since AD12-AD12 willreturn 0?alwaysIF(AD12>2,AD12-2,IF(AD12<=2,

))0

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

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 isB, SUM(B12-B5) and IF(AD12>3,AD12-3,IF(AD12<=3,AD12-AD12))

if Y12 value isC, 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 4Why 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

message edited by mmcconaghy

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

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.

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.

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.

Good luck with your project.

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

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History