[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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
0
nucomputerguy
Asked:
nucomputerguy
1 Solution
 
Paul 1Commented:
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
 
ola_erikCommented:
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
 
Paul 1Commented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Rob HensonIT & Database AssistantCommented:
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
 
byundtCommented:
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
 
nucomputerguyAuthor Commented:
Thanks for the help. Your formulas turned on a light and made it happen! Love the array concept. Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now