Need help with between formula

[nesrawi]

Hi There, need some help with the formula below.

=if(r38 is between 0.5 and 8.5,U42,if(r38>9,0,if(r38 is between 9 and 14.5,v42,if(r38>14.5,0,if(r38 is between 15 and 18.5,w42,if(r38>18,0,if(r38=18.5 +,X42

Thanks

[GC Excel]

Hi nesrawi and welcome to the forum,

Replace your "is between" with this for example
AND(R38 > 0.5,R38 < 8.5)

[nesrawi]

Thank you however i am creating this formula because the pay plan is based on units volume, please see below:

from 0.5 to 8.5 it pays $2000.00
from 9 to 14.5 it pays $4000.00
from 15 to 18.5 it pays $60000
Anything over 18.5 it pays $8000.00

The dollars amount are not accurate it's just an example as the commissions are from 20% up to 35% based on the units volume,

Thanks

[GC Excel]

Ok, then you can use the Lookup formula instead of having multiple IF.
Assuming you set up the following table in A1.

0	0
0.5	2000
9	4000
15	6000
19	8000

Try this formula :
=LOOKUP(D1,$A$1:$B$5)

Where D1 is the volume

[nesrawi]

Please see red arrows.jpg

Hello GC , thanks for the vlookup, however it did not work, what I meant is as follows

ROW 38 is the total of units and row u38 is total commissions, therefore if row r38 is less than 0.5 or over 9 then i need row u38 to show 0, and then if row r38 is between 0.5 and 8.5 then row 38 shows the total amount.

Thanks

[GC Excel]

Hi nesrawi,
I can't see very well what's on your image... better post a sample workbook than a JPG
I still believe the vlookup will work... put the table I suggest somewhere in your tab and refer to it. Example if it's in AA1:BB5

Your formula in U38 could be :
U38 = =LOOKUP(R38,$AA$1:$AB$5)