Solved

Need help with Microsoft Excel 2007 formula

Posted on 2014-01-30
6
293 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 31

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 80

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now