Solved

# Need help with Microsoft Excel 2007 formula

Posted on 2014-01-30
301 Views
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
0
Question by:nucomputerguy
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 3

Expert Comment

ID: 39823316

=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
0

LVL 3

Expert Comment

ID: 39823368
To make it mathematical:

=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
0

LVL 3

Expert Comment

ID: 39823433
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%
0

LVL 33

Expert Comment

ID: 39823912
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
0

LVL 81

Accepted Solution

byundt earned 500 total points
ID: 39824011
Excel wasn't liking your LOOKUP formula because you put cell references inside an array constant. If you pull those out, the formula should work:
=LOOKUP(A1,{10,20,30},A1*{0.25,0.22,0.2}+A1)          how your formula could be converted
=LOOKUP(A1,{10,20,30},A1*{1.25,1.22,1.2})                  alternative conversion

If you are going to take this approach, make sure that you include a 0 bracket. If you don't, the formula returns an error if A1 is less than the smallest value in the first array constant. The first formula below adds a 0 bracket, while the second one converts the 10 bracket into a 0 bracket.
=LOOKUP(A1,{0,10,20,30},A1*{0.30,0.25,0.22,0.2}+A1)
=LOOKUP(A1,{0,20,30},A1*{0.25,0.22,0.2}+A1)
0

Author Closing Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month3 days, 14 hours left to enroll