# 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.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

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

where the weight is in B2.

#### greddy2die

##### New Member
Welcome to the MrExcel forum!

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

where the weight is in B2.

Thank you very much, that worked great!

I really appreciate it!

#### Eric W

##### MrExcel MVP
Glad to help! #### 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)))
=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

</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))

</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!

1,102,774
Messages
5,488,774
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...