Formula Nightmare

[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?

 

[Pete_UK]

I think you will need to give some more information (and details) than
just this.

Pete

 

[IF function incompetent]

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??

 

[IF function incompetent]

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??

 

[Ron Coderre]

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)

 

[Harlan Grove]

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

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

....

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

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

 

[IF function incompetent]

You are a genious! thanks so much for your help!!