Formula Nightmare

  • Thread starter Thread starter IF function incompetent
  • Start date Start date
I

IF function incompetent

I need to compare a range with a number and then have a certain result based
on the range. Can anyone help?
 
Sorry Pete

Here is what I need to do

I am trying to do a similar thing but my values are different.

Can you help me with this one?? :(
 
Sorry Pete

Here is what I am trying to do

The idea is that if J16 equals 0, then G11 will show 0
If J16 is greater than 0 and less than 1, then G11 will show 0
If J16 is equal to 1 but less than 2, then G11 will show 1.5
If J16 is equal to 2 but less than 3, then G11 will show 1.75
If J16 is equal to 3 but less than 4, then G11 will show 2
If J16 is equal to 4 but less than 5, then G11 will show 2.5

Can you help me with this one?? :(
 
Try this:

G11: =CHOOSE(MIN(J16+1,6),0,1.5,1.75,2,2.5,"Invalid: >=5")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
IF function incompetent wrote...
Here is what I am trying to do

The idea is that if J16 equals 0, then G11 will show 0
If J16 is greater than 0 and less than 1, then G11 will show 0
Click to expand...

Since 0 < 1, these could be combined into 'if J16 < 1, G11 shows 0'.
If J16 is equal to 1 but less than 2, then G11 will show 1.5
If J16 is equal to 2 but less than 3, then G11 will show 1.75
If J16 is equal to 3 but less than 4, then G11 will show 2
If J16 is equal to 4 but less than 5, then G11 will show 2.5
Click to expand...
....

Use LOOKUP. Without validation checking (ensuring J16 >= 0),

=LOOKUP(J16,{0;1;2;3;4;5},{0;1,5;1.75;2;2.5})
 
Back
Top