Help With Formula

[Jes271]

Hi,
I am trying to set up a rate spreadsheet.
I have a column with distance
I need to have the answer be x if between this range, and x if between this range. I have about 5 different ranges.
This is what I have tried, but it returns error.( You've entered too many arguments for this function)

Here are the ranges,
If: x is under 350=$950
If: x is 351-450 then multiply by 3.75
If: x is 451-550 then multiply by 3.5
If: x is 551 and up multiply by 3.25

Here is the formula i have been playing around with. =IF(V7>350,v7<450,v7*3.75,if(v7>401,V7<550,V7*3.5,if(V7>551,V7*3.35,950)))
Any help would be appreciated.thanks

 

[RoryA]

Welcome to the forum.

You can do it easily be starting from the bottom band and working up:

=IF(V7<=350,950,If(v7<=450,v7*3.75,if(v7<=550,V7*3.5,V7*3.35)))

 

[AlanY]

would you confirm that for x=450,

will it be 450*3.75 = 1687.5 or
950+100*3.75 = 1325

 

[Jes271]

Ok, let me try it. I will let you know.thanks

 

[Jes271]

Can you add one more for me, so I get it right. over 750 I need it to multiply by 2.75.

 

[RoryA]

=IF(V7<=350,950,If(v7<=450,v7*3.75,if(v7<=550,V7*3.5,if(v7<=750,V7*3.35,V7*2.75))))

If there are any more options than that, I'd suggest a lookup table. Even with this number, it will be easier to maintain.

 

[Jes271]

Thanks RoryA, it is working for what I need. I appreciate your help

 

[jtakw]

Hi,

Or you could "build in the table" with LOOKUP:

Book1
	V	W
7	325	950
Sheet388

Cell Formulas
Range	Formula
W7	=IF(V7<=350,950,V7*LOOKUP(V7,{351,451,551,751},{3.75,3.5,3.35,2.75}))