Solved

Need help with Microsoft Excel 2007 formula

Posted on 2014-01-30
6
296 Views
Last Modified: 2014-01-31
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
Comment
Question by:nucomputerguy
6 Comments
 
LVL 3

Expert Comment

by:Paul 1
ID: 39823316
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
0
 
LVL 3

Expert Comment

by:ola_erik
ID: 39823368
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
0
 
LVL 3

Expert Comment

by:Paul 1
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 33

Expert Comment

by:Rob Henson
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

by:
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

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question