Thanks:  0
Likes:  0

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.

2. 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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•