CODEWITHSUNDEEP
excel
Hannes
Hannes

Reputation: 1

Excel formula for returning a specific value everytime my progressive total increases by a certain amount

I am having trouble creating Excel formula for returning a specific value every time my progressive total increases by a certain amount.

Example: I need to purchase 1300m of piping every time the tonnage profile increases by 15000 tons. Assuming my average tonnage increase is about 2700 per month. My progressive tonnages run from C6:AS6. My first month in C6 is Jan 2015 that runs to Jul 2018. My specific value is in C1. My tonnage increment of 15000 tons is in A3. I tried the following formula but it still requires manual input every next increment (at 30000, 45000, 60000, etc) 

If(C6>A3,C1,if(C6>(A3*2),C1,if(C6>(A3*3),C1,0)))

This formula still creates a problem because I still need to multiply it manually for every increment increase. Is there a way past this?

Upvotes: 0

Views: 98

Answers (1)

Captain
Captain

Reputation: 2218

If you do

=CEILING(C6/$A$3,1)*$C$1

would that give you what you need - the total meters of piping? Breaking it into increments of $A$3 (15000) times by the $C$1 1300m of pipe...

EDIT Original version stepped at >= rather than >...
Using FLOOR(C6/$A$3,1) + 1 means that 15000 would need 2... If the switch should be at 15001 (which, looking at your formula it is - you use CEILING

Upvotes: 2

Related Questions