Any Excel nerds out there that can offer assistance

[Toolin Around]

Gday guys n gals


Got a problem... I'm trying to calculate costing based on volume in an excel spread sheet. The basic details are: I punch in the Length, width and depth of the container the spread sheets produces a total between 100L and 600L. I now need to figure out how to determine the price. The catch is the price isn't linear. 100L will cost more per letre than 200L and so on. I've tried to create an IF Nesting function to take the volume calculated in an above cell and produce a price based on 100 - 200 litres costing X amount and 200 - 300L costing y amount and so on up to 600 Litres. But after struggling with it most of the day I'm not sure if I'm on the right track. Got any ideas.

[Clinton1]

several columns, one for each of the prices?
i.e. a1 = the volume calculation
column b = volume 1lt - 100lt on 100lt cost based formula
column c = volumes 100 - 200lt on 200lt cost based formula
...

Excel is a bit of a dog if you dont have the 'for dummies' manual isn't it?

[duckman]

I'd tackle this problem with a custom function written in VBA.

If you'd like a hand with that, I'd be happy to help. I'll need to know the breaks eg at what volumes does the price change and the price for each volume.

I hope I've explained that clearly. :confused:

Cheers,

Mark.

[chrisb691]

Jeeeeze, he calls you a nerd...then aks for help!!

The attached excel sheet, has an example of a nested formula which has 7 pricebreaks.

In example


Volume to be priced is in A3
Price as per pricebreaks is in C3 (+formula)
Pricebreaks are in cells E1 -> K1

Any problems give me a hoy.

ps. May (probably) be easier ways to do this.

[Toolin Around]

Really appreciate the help guys. I had a look at the formula and I think I was off a bit as to the direction I was going. The break down as to what my parameters are:

The volume will be from 100 to 600 in increments of 100. so 100 to 199.9, 200 to 299.9, 300,399.9, 400 to 499.9... Each of these different volumes will have a different price associated with it. But the prices wouldn't accumulate. They would set the price for all of the volume measured. If that makes any sense (it's late for me). And if that weren't enough I want to also incorperate that if the minimum and maximum were exceeded a "not available" would pop up instead of price.

[ian]

Really appreciate the help guys. I had a look at the formula and I think I was off a bit as to the direction I was going. The break down as to what my parameters are:

The volume will be from 100 to 600 in increments of 100. so 100 to 199.9, 200 to 299.9, 300,399.9, 400 to 499.9... Each of these different volumes will have a different price associated with it. But the prices wouldn't accumulate. They would set the price for all of the volume measured. If that makes any sense (it's late for me). And if that weren't enough I want to also incorperate that if the minimum and maximum were exceeded a "not available" would pop up instead of price.

If you are saying that a container of volume 190l is calculated using the same rate as for one of 120l, but a different rate is used for a 230l container, then the IF statements would be (with the volume calculated in cell C3, and the prices in cells A5=price for 100 to 200l, to A9=price for 500 to 600l)

IF(OR(C3<100,C3>600),"not available",IF(C3<200,C3*A5,IF(C3<300,C3*A6,IF(C3<400,C3*A7,IF(C3<500,C3*A8,C3*A9)))))

but you should also consider rounding the result to the next highest 1, 5 or 10.


ian

PS I object to being refered to as an Excel nerd. I cut my programing teeth using Fortran and my spreadsheet knowledge is mostly from Lotus123. To this day, Excel has not replicated all the really useful 123 functions, and the only thing it does better is auditing of formulas.

[chrisb691]

Really appreciate the help guys. I had a look at the formula and I think I was off a bit as to the direction I was going. The break down as to what my parameters are:

The volume will be from 100 to 600 in increments of 100. so 100 to 199.9, 200 to 299.9, 300,399.9, 400 to 499.9... Each of these different volumes will have a different price associated with it. But the prices wouldn't accumulate. They would set the price for all of the volume measured. If that makes any sense (it's late for me). And if that weren't enough I want to also incorperate that if the minimum and maximum were exceeded a "not available" would pop up instead of price.

New version handles this. The error I put in is "N/A".

[Wongo]

Mat, is this what you want? Goto Tools, Macro, Visual Basic editor and click on module1 to see the code. It is pretty simple and you should be able to work it out.

cheers

[ian]

Chris

you don't need the AND statements as they are implied by the way Excel determines a logical statement.
so having excluded the "not availale" options (<100, >600), the next test can be is volume<200, if TRUE calculate the price, if FALSE test the next price step volume<300 and so on

ian

[Wongo]

Forgot to say that fuction "find_cost" works like a standard Excel function just like Sum, Average, etc. You can use it anyway you like.

And it should be volume not volumn.

[Wood Borer]

A Vertical Lookup table would also suit this application, easy to use and easy to change values plus one simple formula.

[Wood Borer]

I have to race off to an all day meeting but here is an example using Vlookup

[Wongo]

TA, we need to upgrade the macro.

The second last case should be "Case Is <= 600" not "Case Is < 600"

Case Is <= 600
rt_value = in_volume * cost_factor(5)