Solved

Need help with Microsoft Excel 2007 formula

Posted on 2014-01-30
6
294 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to get AD RMS to work with Office 2016 for Mac 6 146
SQL Exceptions 3 35
Excel 2016 Not Responding Issues 6 22
Web Form VB.Net  import CSV 4 17
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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: …

937 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

4 Experts available now in Live!

Get 1:1 Help Now