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

#### 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,084,744
Messages
5,379,588
Members
401,614
Latest member
priokatm

### This Week's Hot Topics

• VBA code giving errors and stopping Excel
Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
• Disable MsgBox message
Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
• Macro Recorder into VBA, Copy Paste Data Filled Cells
Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
• Number format changes while pasting into a cell
Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
• Collating data
Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
• Sum Multiple Columns Based on Multiple Criteria
I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...