If Statement returning False not the value required

Question

I cannot get this formula to work i have tried it 2 ways by a Vlookup from the table and also inserting the text direct into the formula. I would preferr to use a table to look up to return the text.

I need to put a number in from 1-100 in coulmn H and if it falls betwen certain values it returns the text

=IF(H15<=4,"0-4MWh=10%",IF(H15>4<=15,"4-15MWh=12%",IF(H15>15<=30,"15-30MWh=14%",IF(H15>30<=50,"30-50MWh=6%",IF(H15>50<100,"50-100MWh=18%")))))

I would prefer to us a table like below i have calles it "COMS" if a look up formula was used

Can someone please help

Thanks,

Mick

Answer 1

Try,

=IF(H15="","",IF(H15<=4,"0-4MWh=10%",IF(H15<=15,"4-15MWh=12%",IF(H15<=30,"15-30MWh=14%",IF(H15<=50,"30-50MWh=6%",IF(H15<100,"50-100MWh=18%","<out of range>"))))))

... since an IF statement is processed proressively, there is no need to stategreater than 4 and less than or equal to 15. If you needed two criteria for another application, then,

=IF(H15="","",IF(H15<=4,"0-4MWh=10%",IF(AND(H15>4,H15<=15),"4-15MWh=12%",IF(AND(H15>15,H15<=30),"15-30MWh=14%",IF(AND(H15>30,H15<=50),"30-50MWh=6%",IF(AND(H15>50,H15<100),"50-100MWh=18%"))))))

I also added a condition to do nothing if H15 is blank.

---

• If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

Answer 2

I cannot get this formula to work i have tried it 2 ways by a Vlookup from the table and also inserting the text direct into the formula. I would preferr to use a table to look up to return the text.

 

I need to put a number in from 1-100 in coulmn H and if it falls betwen certain values it returns the text

=IF(H15<=4,"0-4MWh=10%",IF(H15>4<=15,"4-15MWh=12%",IF(H15>15<=30,"15-30MWh=14%",IF(H15>30<=50,"30-50MWh=6%",IF(H15>50<100,"50-100MWh=18%")))))

I would prefer to us a table like below i have calles it "COMS" if a look up formula was used

0 – 4MWh = 10%	10.00%
4 – 15MWh = 12%	12.00%
15 – 30MWh = 14%	14.00%
30 – 50MWh = 16%	16.00%
50 – 100MWh (QLD) – 160MWh (VIC/ NSW/ SA) = 18%	18.00%

Can someone please help

Thanks,

Mick

Assume that the text are in column A1 to A5, my lookup cell is at C1. Try this formula =IF(C1<=4,A1,IF(C1<=15,A2,IF(C1<=30,A3,IF(C1<=50,A4,A5))))

Answer 3

A	B	C	D
4	0-4	10%
5	5-15	12%
16	16-30	14%
31	31-50	16%
50	51-100	18%
		4	10%

enter value in C9

with formula in D10

=VLOOKUP(C10,$A$2:$C$6,3)

don't think it work if the bands have overlaps (e.g. 0-4 and 4-15) where if value = 4 don't know which band belongs to

Answer 4

I cannot get this formula to work i have tried it 2 ways by a Vlookup from the table and also inserting the text direct into the formula. I would preferr to use a table to look up to return the text.

 

I need to put a number in from 1-100 in coulmn H and if it falls betwen certain values it returns the text

=IF(H15<=4,"0-4MWh=10%",IF(H15>4<=15,"4-15MWh=12%",IF(H15>15<=30,"15-30MWh=14%",IF(H15>30<=50,"30-50MWh=6%",IF(H15>50<100,"50-100MWh=18%")))))

I would prefer to us a table like below i have calles it "COMS" if a look up formula was used

0 – 4MWh = 10%	10.00%
4 – 15MWh = 12%	12.00%
15 – 30MWh = 14%	14.00%
30 – 50MWh = 16%	16.00%
50 – 100MWh (QLD) – 160MWh (VIC/ NSW/ SA) = 18%	18.00%

Can someone please help

Thanks,

Mick

Your table is confusing. It shows overlapping intervals.

Are the values being looked up always integers (whole numbers)?

Should your table maybe be:

0 to 4 = 10

5 to 15 = 12

16 to 30 = 14

31 to 50 = 16

51+ = 18

The formula could be as easy as:

=LOOKUP(H15,Coms)

But it depends on getting the table intervals defined properly.

--

Biff

Microsoft Excel MVP