New at Excel - help with an addition/multiplication nested formula

greddy2die

New Member
Hows it going everyone, i'm VERY new to creating Excel formulas (less than 24 hours as of now). I've started to create a spreadsheet in Excel for my coworkers to eventually use in order to provide our clients costs for various services quickly & to ensure there aren't any errors in the calculations.

Anyway, i'm stuck on finding a way to make this final calculation "dummy proof" & not sure if there are multiple IF and/or OR formulas to use nested together to create what I imagine.


What i'm trying to get Excel to calculate:
I have a cost of something that increases by $165 over the base price of $4675 for every 1,000 tons over 30,000 tons. Example, it something is 31,000 tons then the price is $4840, if the weight is 32,000 then $5005... so on & so forth.

I have a cell for the weight at the top of my sheet that this area would reference from & for this certain calculation it just needs to round up to the nearest thousandth to work.
Example, if I entered a weight of 32,356 i'd like it to round up to 33,000 then start its calculation of adding the $165 for every 1k over 30k.


Thanks for any info or help you can provide!
 

Eric W

MrExcel MVP
Welcome to the MrExcel forum!

How about:

=4675+MAX(0,ROUNDUP((B2-30000)/1000,0)*165)

where the weight is in B2.
 

greddy2die

New Member
Morning,

One final (hopefully) question in regards to this. You're formula works great however I was wondering if there was another formula that may achieve the same thing? I only ask because for this certain table I made to calculate costs, if the total weight is under 30,000 its still calculating costs in the 30,000+ column so I'm getting double figures. Below are the formulas i'm using so you have an idea of what I mean.

=IF(C3<20001,SUM(G61*C9))
=IF(C3>20000,IF(C3<22501,SUM(G62*C9)))
=IF(C3>22500,IF(C3<25001,SUM(G63*C9)))
=IF(C3>25001,IF(C3<27501,SUM(G64*C9)))
=IF(C3>27501,IF(C3<30001,SUM(G65*C9)))
(finally, your provided formula)
=4685+MAX(0,ROUNDUP((C3-30000)/1000,0)*165)


 

Eric W

MrExcel MVP
First, I can tell that you're pretty new to formulas. What you have is workable, but has potential for improvement. For example, none of your formulas requires the SUM function. The SUM function adds up 1 or more values. the result of G62*C9 is a single value, so when you SUM it, it returns that same value.

Next, while you can work with IF, this is an ideal example of when to use a lookup function. Try this:

Excel 2012
ABCDEFGHI
1Range
2WeightCostLowHighCost
3350005510120002900
4551020001225003550
522501250003815
625001275004260
727501300004685
83000199999999Variable

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
D3=IF(C3<$G$8,VLOOKUP(C3,$G$3:$I$8,3),4685+MAX(0,ROUNDUP((C3-30000)/1000,0)*165))
D4=IF(C3<30001,LOOKUP(C3,{1,20001,22501,25001,27501},{2900,3550,3815,4260,4685}),4685+MAX(0,ROUNDUP((C3-30000)/1000,0)*165))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



The D3 formula decides if the weight is under 30,001, and if so, uses the VLOOKUP to find the right cost from the table in G1:I8. If not, it uses the formula I provided before.

It is possible to actually embed the table within the formula, like in the D4 formula. But I'd consider this to use some advanced techniques. Moreover, if you ever change the rates or ranges, with the first formula you can just change the table. With the second formula, you have to find the formula on the sheet, and manually change the actual formula.

Hope this helps!
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top