asked on

Need help with Microsoft Excel 2007 formula

I have an Excel database with a single column showing different product cost and I want to add markup to each product cost based on that product cost.

For instance if the cost of the product is less than $10 then markup is 25%, but if cost is >$10 but < than $20 markup is 22%, if cost is >$20 but <$30 then markup is 20%, etc. And the result of the calculation put in a seperate column.

Was considering using the =LOOKUP but not sure of the formula:
Here's what I came up with but Excel doesn't like it. (A1 is one cell with the actual Cost).

=LOOKUP(A1,{10,20,30},{A1*.25+A1,A1*.22+A1, A1*.20+A1})

I'm new at Excel and would appreciate any help! Thanks

Paul 1

your value in cell A1

=IF(A1>30,A1*1.2,IF(A1<10,A1*1.25,A1*1.22))

first 'if' deals with value > 30 and if its not the else portion evaluates next
Second 'if' deals with value < 10 and 'else' has to be in the 22% range

Edit the 30 and 10 as required

ola_erik

To make it mathematical:

price range markup adjusted price
=trunc(a1/10) =25-(b1*2) =a1*(1+(c1/100)) or somethingd
5 0 25
12 1 23
14 1 23
16 1 23
25 2 21
35 3 19

Paul 1

I just re-read the question and made a mistake on the origional calculation
But the nesting of if statements should work with a little adjustment

=IF(A1<10,A1*1.25,IF(AND(A1>10,A1<20),A1*1.22,IF(AND(A1>20,A1<30),A1*1.2,A1*1.18)))

I set the last range( >30) to 18%

Rob Henson

See attached use of VLOOKUP with a small table of values to lookup.

Examples in column A, all doing lookup in G1:H5

Thanks
Rob H
Lookups.xlsx

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Walters

ASKER

Thanks for the help. Your formulas turned on a light and made it happen! Love the array concept. Thanks