Pricing formula

Hi All,

Although I have been using Excel at a simple level for a number of years I am very new to VBA and would like to ask for some pointers on a pricing problem.

To explain, if I cost a full sheet of print at 2440 x 1220mm (8ft x 4ft) at £30 for the board and £160 for the print this gives me a cost of £190.00. Which is about right for the industry.

However, if I divide this by 32 to get a square foot rate I get £5.93, which is too low. If I use the current price of £12.97 square foot an 8ft x 4ft works out at £415.00 which is way to expensive.

I'd like to put a spread sheet together that the girls in the office can use to enter the height and length of a sign in either inches or millimetres and get an accurate and price. Non of the formulas seem to be capable hence me exploring VBA.

Any help or advice would be greatly appreciated.

Dave

Thanks for the response Shg,

That's the approach I've been playing with, but when I use it to price up a small sign of say 100 x 50mm the cost is too low.

----A---- ---B---- ---------------C---------------
1 Basis
2 width 2,440 B2: Input
3 height 1,220 B3: Input
4 area [m2] 2.977 B4: =B2*B3/1000000
5 price $190.00 B5: Input
6 price/m2 $63.83 B6: =B5/B4
7
8 Example
9 width 100 B9: Input
10 height 50 B10: Input
11 price $00.32 B11: =$B$6 * B9 * B10 / 1000000

Is there a way to automatically lower the cost per square metre as the sign size gets bigger?