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

greddy2die

New Member
Joined
Oct 18, 2019
Messages
8
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the MrExcel forum!

How about:

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

where the weight is in B2.
 
Upvote 0
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)


Screenshot.jpg
 
Upvote 0
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
Range
WeightCostLowHighCost
Variable

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]5510[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2900[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5510[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]3550[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22501[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]3815[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25001[/TD]
[TD="align: right"]27500[/TD]
[TD="align: right"]4260[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]27501[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]4685[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30001[/TD]
[TD="align: right"]99999999[/TD]

</tbody>
Sheet3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(C3<$G$8,VLOOKUP(C3,$G$3:$I$8,3),4685+MAX(0,ROUNDUP((C3-30000)/1000,0)*165))
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=IF(C3<30001,LOOKUP(C3,{1,20001,22501,25001,27501},{2900,3550,3815,4260,4685}),4685+MAX(0,ROUNDUP((C3-30000)/1000,0)*165))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top