I think I need a "If" Formula

[elizabethkarn]

Hi,
I'm struggling to come up with an IF formula that works (If that's the right formula to even use), and I need an expert to point me in the right direction. I'm trying to come up with an price calculator that will calculate a pallet rate or a lift rate depending on the cubic volume or the weight. A pallet can be from 0 to 1.44 m3 (metre cube) and/or 0 to 500 Kilograms. Once either of those weights are exceeded a Lift Rate will be charged. A lift rate is 1.45 to 2.16 m3 and/or 501 to 1000 Kilograms. Here's a snip of the excel spreadsheet we're currently using:
We want people to beable to enter B15 and E15 with their m3 and Kg and then that will determine whether it picks up a pallet or lift rate or the minimum charge, this would then be published in H21 and would populate the rate down to H31 etc.

Hope you guys can help me, I'm really stuck on this!
Also, thanks in advance to anyone who can offer me some guidance

 

[DSCfromCFA]

G'day Elizabeth,

It may help if you give us some examples of how the lift rate is applied I'm assuming the scenario is something like:
Pallet < 500 kg and < 1.44 m3, minimum rate.
Pallet >500 kg but < 1.44 m3 minimum rate + extra cost - flat? percentage? sliding?
Pallet <500kg but > 1.44 m3 minimum rate + extra cost - flat? percentage? sliding?
Pallet >500kg and > 1.44m3 minimum rate + extra cost - flat? percentage? sliding?

You also say you want it to auto fill down the column H21:H31 - based on what?

Hopefully this will assist in getting a solution.

Cheers

shane

 

[elizabethkarn]

Thanks so much for your reply! Hope this will make things a little clearer.
First scenario is correct, for scenario two if it was greater than 500 kg it would then get classed as a lift (not all of our destinations have a lift rate). If we don't have a Lift Rate set for them then that would get charged 2 pallets (2x the Pallet Rate). Scenario three is the same as Scenario two as it can be a max of 500 kg or 1.44 m3. Fourth scenario then this would either get classed as 2 Pallets or if a lift rate is available and it was less than 1000 kg and 2.16 m3.
For example, If this was going ex Auckland to Jonhsonville scenario 1 would rate up as $216.45. scenario 2 would rate at $281.39. Scenario 3 would rate the same as Scenario 2. And scenario 4 would rate as a lift at $281.39.
If we were sending the goods from Auckland to Fielding, we have no Lift Rate attributed to this destination therefor it should say "Not Avaliable" and the client would have to put it into our system as 2 x Pallets.
Thanks so much in advance for all your help!!
Happy to email the spreadsheet to you if it helps.

 

[DSCfromCFA]

Thanks Elizabeth,

To be clear - based on the above and the spreadsheet example,

Only two destinations have a Lift Rate - Johnsonville & Wellington.
The lift rate is applied only if the pallet exceeds the 500/1.44 but is less than 1000/2.16
If the pallet exceeds 1000/2.16 then it must be put in as two pallets so this should also flag as "Not Available" in column H

The other destinations have a pallet rate and if the pallet exceeds 500/1.44 they have to input it as two pallets so a "Not Available" message should appear in Column H.

If I have all that correct I will try and work something up, but it will be quite a long formula and may take me a bit of time. Of course one or more of the experts may jump in and provide a much more elegant solution than what I am capable of providing.

regards

shane

 

[yky]

Thanks so much for your reply! Hope this will make things a little clearer.
First scenario is correct, for scenario two if it was greater than 500 kg it would then get classed as a lift (not all of our destinations have a lift rate). If we don't have a Lift Rate set for them then that would get charged 2 pallets (2x the Pallet Rate). Scenario three is the same as Scenario two as it can be a max of 500 kg or 1.44 m3. Fourth scenario then this would either get classed as 2 Pallets or if a lift rate is available and it was less than 1000 kg and 2.16 m3.
For example, If this was going ex Auckland to Jonhsonville scenario 1 would rate up as $216.45. scenario 2 would rate at $281.39. Scenario 3 would rate the same as Scenario 2. And scenario 4 would rate as a lift at $281.39.
If we were sending the goods from Auckland to Fielding, we have no Lift Rate attributed to this destination therefor it should say "Not Avaliable" and the client would have to put it into our system as 2 x Pallets.
Thanks so much in advance for all your help!!
Happy to email the spreadsheet to you if it helps.

When sending goods from, say, Auckland to Fielding, does it matter whethere Auckland has lift? Or only destination is of concern?

You also mentioned minimum charge. How does it work?

 

[elizabethkarn]

Thanks Elizabeth,

To be clear - based on the above and the spreadsheet example,

Only two destinations have a Lift Rate - Johnsonville & Wellington.
The lift rate is applied only if the pallet exceeds the 500/1.44 but is less than 1000/2.16
If the pallet exceeds 1000/2.16 then it must be put in as two pallets so this should also flag as "Not Available" in column H

The other destinations have a pallet rate and if the pallet exceeds 500/1.44 they have to input it as two pallets so a "Not Available" message should appear in Column H.

If I have all that correct I will try and work something up, but it will be quite a long formula and may take me a bit of time. Of course one or more of the experts may jump in and provide a much more elegant solution than what I am capable of providing.

regards

shane

Yes your reply is 100% correct, thanks for your help so far, would be great if you could come up with a formula

Regards Libby

 

[elizabethkarn]

When sending goods from, say, Auckland to Fielding, does it matter whethere Auckland has lift? Or only destination is of concern?

You also mentioned minimum charge. How does it work?

Nope, it doesn't matter where the consignment has come from when it comes to applying lift and pallet charges only where it's going to. Minimum is just the lowest rate we would apply to any consignment going to those destinations. So, if someone was sending something that only weighed 1 Kg, it would not make economical sense to charge them this based on weight because we would not be able to recoup the cost, so a minimum charge is charged.
I hope that this makes sense! If not, I'd be more than happy to answer whatever questions you may have!

 

[DSCfromCFA]

G'day Libby,

Give this a try for H21 and copy down to H31 NOTE - remove the spaces from around the < symbol before you copy down
=IF(AND($B$15 < =1.44,$E$15 < =500),$B21,"Not Available")

Then paste these in to H26 & H31 Again remove the spaces from around the < & > either before or after you paste.
=IF(AND($B$15 < =1.44,$E$15 < =500),$B26,IF(AND(OR($B$15 > 1.44,$E$15 > 500),AND($B$15 < =2.16,$E$15 < =1000)),$D26,"Not Available"))
=IF(AND($B$15 < =1.44,$E$15 < =500),$B31,IF(AND(OR($B$15 > 1.44,$E$15 > 500),AND($B$15 < =2.16,$E$15 < =1000)),$D31,"Not Available"))

Note that this is written so that if either 2.16 or 1000 is exceeded you get the not available comment as I figure that is more likely than both being exceeded before you won't ship it.

I would also recommend putting some data validation on B15 and E15 to make sure a negative number cannot be entered.
The "Not Available" message could also be altered to something more informative.

Cheers

shane

 

[elizabethkarn]

Shane, thank you so, so much!! You're an actual life saver, it worked perfectly. Seriously, thank you x a million!

 

[DSCfromCFA]

You're welcome Libby, and thanks for the feedback.