Excel novice

[AndrewLutz34]

Admittedly, I am a novice at Excel, but need help with a formula. I need to have a formula that can calculate the following:


if "cell value" is between 0 and 3.99, it says "24"

between 4 and 5.24, it says "18"

between 5.25 and 6.99, it says "12"

between 7 and 8.49, it says "6"

over 8.5, it says "0"


Help?!

 

[Luke M]

First, setup a table like so:

[pre]

0       24
4       18
5.25    12
7       6
8.5     0

[/pre]
Assuming this table is in C2:D6, and the value you are looking at is in A2, formula is:

=LOOKUP(A2,$C$2:$D$6)


Formula/table works by setting the lower threshhold for each corresponding value. You could manually type everything into the table, but IMO it's easier and better practice to have a table so you can see what's been set, and it's easier to change in the future.

 

[Raja Kamarthi]

Hi Andrew,


Please apply the below formuale (assuming A1 has the value)


=IF(A1<=3.99,24,IF(A1<=5.24,18,IF(A1<=6.99,12,IF(A1<=8.49,6,0))))


Hope this should help you.


Regards,

Raja

 

[AndrewLutz34]

One more question: I need to add up the results of that formula for multiple cells. Possible? I tried the basic sum function, but got nowehere

 

[Luke M]

Lets say the values you are comparing are in A2:A10, and the LOOKUP formula is in B2:B10. Formula to get total would be:

=SUM(B2:B10)

 

[AndrewLutz34]

I used Raja's method

 

[Luke M]

That's fine. Same idea works for getting the total. =)

 

[AndrewLutz34]

All set; thank you!