Help with formula ???

hahaa

New Member
Joined
Feb 20, 2002
Messages
1
I'm new to this board and hope I can find the help I need to solve some basic problems I have with understanding Excel.

I'm working on a project comparing proposed cost vs current cost. I would like to enter a logic formula as follows.

If the units in a column are less than 100 the rate of $193.00 would display. If the units are between 100-300 than a rate of 194.00 would display. This same logic would follow for 300-500, 500-700 and over 700 units with increasing rate allpying to each level.

Any help would be appreciated.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
If the units in a column are less than 100 the rate of $193.00 would display. If the units are between 100-300 than a rate of 194.00 would display. This same logic would follow for 300-500, 500-700 and over 700 units with increasing rate allpying to each level.

This formula will do what you want:

=IF(A1<100,193,IF(A1<300,194,IF(A1<500,195,IF(A1<700,196,197))))

Caveat: Excel will only let you next six functions. If you have a lot of categories, you can use a lookup table:

put in D1 to E5:
D1: 0, D2: 100, D3: 300, D4: 500, D5: 700
E1: 193, E2: 194, E3: 195, E4: 196, E5: 197

Now =VLOOKUP(A1,$D$1:$E$5,2,TRUE)
will get you what you want. You need
to make sure that the numbers in D1:D5
are ascending, but this method will handle
an indefinite number of categories.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top