Excel spreadsheet

[KyaJae]

Might be a little ling winded but please bare with me.

I am almost finished with a calculation sheet for my co-workers that will help them with quoting jobs.

This is for the supply and installation of security screens. I have so far created it so that there is a total cost for the 3 different types of security screens based on sizes and type, however if we are only installing between 1 and 3 screens, we add on a charge. I have made this to only reach a limited total but just wondering how I would take off this amount if we are to install more than 3.


I hope this makes sense!

 

[Michael M]

How are you implementing the add on charge, by formula ?
If so, what is the formula ?

 

[t0ny84]

Hi KyaJae,

This formula may help depending on how you have the columns set out.

=IF(F2<3,SUM(F2*G2)+0.5,SUM(F2*G2))
If the amount of screens is under 3, amount x unit price + surcharge (0.5), if amount is over 3 then amount x unit price only.

 

[KyaJae]

I have a cell that has a formula to add the amount of screens in total but to only be limited to the cost of 3 max. Sorry if I am not explaining myself correctly - Im only knowledgeable in basic formulas. so the Hidden cell that is calculating this reads - =MIN(280.5,(H29*93.5))

 

[KyaJae]

T

Hi KyaJae,

This formula may help depending on how you have the columns set out.

=IF(F2<3,SUM(F2*G2)+0.5,SUM(F2*G2))
If the amount of screens is under 3, amount x unit price + surcharge (0.5), if amount is over 3 then amount x unit price only.

View attachment 20779

Thank you - the only thing I cant get passed is adding on either a charge for 1, 2, 3 or 0 screens. :/

 

[KyaJae]

Because there is no set "surcharge" I am not sure how to set it out to show either the charge for 1, 2, 3 or no install

 

[t0ny84]

Hi KyaJae,

How does the surcharge work? e.g. If only 1 door they pay an extra 10%, if 2 doors 5% extra, 3 doors 2.5% extra?

 

[KyaJae]

Hi KyaJae,

How does the surcharge work? e.g. If only 1 door they pay an extra 10%, if 2 doors 5% extra, 3 doors 2.5% extra?

There is a $93.50 charge per screen up to 3.

 

[t0ny84]

There is a $93.50 charge per screen up to 3.

Are the doors all the same cost?

 

[KyaJae]

Are the doors all the same cost?

No, they are calculated by m2 (as they are screens for windows and all windows are different sizes)