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

[greddy2die]

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]

Welcome to the MrExcel forum!

How about:

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

where the weight is in B2.

 

[greddy2die]

Welcome to the MrExcel forum!

How about:

=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]

Glad to help!

 

[greddy2die]

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]

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

	A	B	C	D	E	F	G	H	I
Range
Weight	Cost	Low	High	Cost
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!