Hey,

I need to get a formula or formatting for:

I have data which is calculated from one sheet onto another sheet.
Within that sheet I have that data which I need to transfer that figure into another cell. (one of 4 or 5)
Now the problem I can't solve or find a result for is:
If the number is between lets say 1-99 it needs to go into a specific cell for further calculation.
If the number is between 100-199 it needs to go into a specific cell different to the one above it.
and so on.
Making sense?



MS EXCEL 2010

I used to be able to do it in the old 2003 version under conditional formatting but I can't seem to work it out/find the right bit in this version.

I want to try to avoid it but I can screenshot what I need to do if it makes no sense.

actually what I posted above will work for you (I looked at your screencapture) but rather than columns just think of them as cells. You can also get it to work for ranges by using if statements inside on another.

I.e. this would net you any value from 100 to 198

=if(A1>99,if(A1<199,A1,""),"")

It's correct, but nested IFs in excel formulas make baby jesus cry

=IF(AND(A1>99,A1<199),A1,"")

The formula should be in the destination cell.

nested IFs in excel formulas make baby jesus cry

True that. They can only be nested 7 deep by the way.

I prefer to use * instead of and only because it's closer to typing &&

=IF((A1>99)*(A1<199),A1,"")

Why does that work ?
1 is true (actually anything not 0)
0 is false

1 * 1 = 1
true and true = true

false and anything = false
0 and anything = 0


or is +

Seriously ... 64 ... fuck me 7 was stupid I have no idea why you would need 64.

You can use vlookup like a switch case too (but not in this scenario).

SUMPRODUCT is even better