using IF or ELSE function on excel

[conorm]

Hi another Excel question, this is quite a long-winded one but I'm looking to populate some cells depending on a specific set of circumstances and am not quite sure how to do it.


He is the thing is the question say cells have in them the following;

A1 = 3.5
B1 = 3.8
C1 = 4.1
D1 = 4.5

Is there any way I can say in cell E3, IF E1+E2- is between 100-124 then use a1, if it is between 125-150 then use B1, if it is between 150-200 then use C1 etc...

And also to make it even more complicated can I also say as an overall function to the whole cell after it has selected the appropriate value from above, if E5 = 2 then add 1, if E5 = 3 add 1.

So basically I want to know if you can use multiple If functions in one cell and instead of just saying IF e5= a otherwise b. I want to say IF e5 = this then a, otherwise b, otherwise c, otherwise d

[beepbeep20]

=IF(AND(E1+E2>=100,E1+E2<125),IF(E5=2,A1+1,IF(E5=3,A1+2,A1)),IF(AND(E1+E2>=125,E1+E2<150),IF(E5=2,B1+1,IF(E5=3,B1+2,B1)),IF(AND(E1+E2>=150,E1+E2<200),IF(E5=2,C1+1,IF(E5=3,C1+2,C1)),IF(E1+E2>=200,IF(E5=2,D1+1,IF(E5=3,D1+2,D1))))))

That's what I think you're looking for if I follow your question correctly, it's a pretty unwieldy function especially if you need to modify part of it.

I'd check properly that it does what you want before putting any money on it though (I didn't check).

[beepbeep20]

The forum cut off the text because it's too much for one line I presume.

You'll need to remove the linebreaks/spaces. What's below should all be on one line.

=IF(AND(E1+E2>100,E1+E2<125),
IF(E5=2,A1+1,IF(E5=3,A1+2,A1)),
IF(AND(E1+E2>=125,E1+E2<150),
IF(E5=2,B1+1,IF(E5=3,B1+2,B1)),
IF(AND(E1+E2>=150,E1+E2<200),
IF(E5=2,C1+1,IF(E5=3,C1+2,C1)),
IF(E1+E2>=200,IF(E5=2,D1+1,
IF(E5=3,D1+2,D1))))))

[Euler]

It may be a little neater to create a truth table and then use if commands to trigger from that truth table?

[conorm]

Thanks a lot, I'll give it a go. appreciate the help.

[conorm]

Hi it almost works but for some reason the very last section of commands is returning errors? This is the formula I've put in and it works fine like this, but if I want to add one more line at the end that reads:

IF(AND(B10-C4>=200,B10-C4<=500),IF(B11=3,P10-1,IF(B11=5,P10+0.5,P10))

Complete formula, minus bit at the top which works, but when I add the last line above it doesn;t work, any ideas, is it too many if's for one formula?:

=IF(AND(B10-C4>=0,B10-C4<=75),IF(B11=3,P4-

1,IF(B11=5,P4+0.5,P4)),IF(AND(B10-C4>=75,B10-

C4<=100),IF(B11=3,P5-

1,IF(B11=5,P5+0.5,P5)),IF(AND(B10-C4>=100,B10-

C4<=125),IF(B11=3,P6-

1,IF(B11=5,P6+0.5,P6)),IF(AND(B10-C4>=125,B10-

C4<=150),IF(B11=3,P7-

1,IF(B11=5,P7+0.5,P7)),IF(AND(B10-C4>=150,B10-

C4<=175),IF(B11=3,P8-

1,IF(B11=5,P8+0.5,P8)),IF(AND(B10-C4>=175,B10-

C4<=200),IF(B11=3,P9-1,IF(B11=5,P9+0.5,P9))))))))

[conorm]

Also one other thing with the formula I mentioned below it produces an error in the few situations when b10-c4 is a negative number, is there any way of saying if the number is negative then just look at cell B10?

Or is this too complicated?

And does anyone have any ideas why the formula would accept this last line of the formula for the original formula I was asking about?

IF(AND(B10-C4>=200,B10-C4<=500),IF(B11=3,P10-1,IF(B11=5,P10+0.5,P10))

thanks again for everyones help

[beepbeep20]

I think the negative number problem is fairly easy to correct, assuming it returned 'FALSE' how it was before.

The end of your formula should be amended to read )),0)))) and not )))))), so just add the ,0 (if you want it to give something else than zero as the default value then put in whatever you decide is best for the situation)

Yes there is a limit on nesting IF functions, I believe currently it's 7, so that might be the problem. If I count correctly in your version you would want 8 x IF(AND(...)

I think you may be better off dividing this up a bit into separate cells or using a truth table as Euler suggested.

You could (for instance, using example values) in one cell evaluate whether the value is only between 0->100, 101->200, 201->300 and in another cell evaluate only between 301->400, 401->500, 501->600 etc.

Doing it that way would allow another cell to pick the value you're looking for from just 2 choices. Tracking down either a typing or logic error would be easier that way too.

These functions become so unwieldy that editing them is very difficult and once excel decides there's an error, tracking down where/what the problem is can be tricky.

[conorm]

Hi beepbeep, I took your advice and managed to compact two areas down into one so that solved the too many IF's error but in regards to the second part what I want to do is instead of just returning a 0 if the answer is false is to assign it a number if it's false. So basically to say if it is false or 0 then if b1 which is the cell above it = between 0-100 show cell d1, if it's between 100-200 then cell d2 and if it's between 200-300 then cell d3?

Is this possible?

[conorm]

If it is not possible, how would I go about designing a truth table to incorporate all this info, I'm so close now except for that last bit?

[pdupre1961]

Hi Conor,

Just use a vlookup table. Simples...

Paul

[mikelenard]

Paul,

I have no choice but to use another Excel spreadsheet and use the vlookup function. Primarily because of non-runners, number or runners and the SP value. Could you provide an example of a vlookup formula?

Thanks,

Mike