IF and AND functions request for help

Closed
mazestix - Sep 6, 2016 at 06:06 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 8, 2016 at 11:01 AM
Dear all,

I am trying to learn more on how to use Excel and set some basic plannings on it. Unfortunately, I'm not good enough and for some special requests I cannot find answers in my language. If any reader has the time to reply to my following problem I would be more than happy (thank you in advance)
I have a row with cells with different numbers and under it there is another row that takes sub-sums (=B2+A3). I'd like to make an IF formula under this row to say that
IF 1.0<B2<2.0 write A or leave the cell blank
IF 3.0<B2<4.0 write B or leave the cell blank
IF 6.0<B2<7.0 write C or leave the cell blank
IF 8.0<B2<9.0 write D or leave the cell blank
IF 11.0<B2<13.0 write F or leave the cell blank
I don't need more than 5 different results. I managed to give a solution using the following formula:
=IF(AND(B2>1,B2<2),"A",IF(AND(B2>3,B2<4),"B",IF(AND(B2>6,B2<7),"C",IF(AND(B2>8,B2<9),"D",,IF(AND(B2>11,B2<13),"E"" ")) ))
However, this quite complicated to maintain in the future if you want to change parametrs. Does anyone know a better way to make a function like this working?

PS: I have made the same post a few hours ago but because of my need of getting this thing done immediately I forgot to be polite. I apologize, thank you Ambucias for closing the previous one.

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 8, 2016 at 11:01 AM
Hi mazestix,

You can put your parameters in a range and then refer to those cells.

So put 1, 2, 3, 4, 6, 7, 8, 9, 11, 13 in a column like H and change your formula into: =IF(AND(B2>H2,B2<H3),"A", ... etc..

To prevent the reference of the parameters to change when dragging the formula, hit F4 when inputting/selecting the reference or manually put $ symbols in front of the row and/or column: =IF(AND(B2>$H$2,B2<$H$3),"A", ... etc..

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0