# Thread: Setting a minimum area charge

1. ## Setting a minimum area charge

Hi
I'm trying to help a colleague of mine who struggles with numbers, by giving him this glass pricing sheet. I have managed to use Youtube video's to get this far with the sheet, but I have no idea at all of where to start with the most important calculation.
I'm trying set a minimum area charge on the sheet. I've tried to explain as best i can on the sheet attached.
I really really would appreciate any help/guidance you can give me.
Regards
Stuart

2. If it was my project I'd construct the file like the attached. (I completely overhauled your workbook). Glad to discuss it if it's something you can work with.

3. Don't know if you're interested in the approach I took, but I worked on it some more (enjoyed working on a relatively simple applications for a change). Updated file attached.

4. Hi Ron
Just wanted to say a big thank you for all your help with this worksheet. I have to say with my limited knowledge of excel i have no idea of what/how all the formulas you have created work. On the last sheet you sent I noticed that the minimum area part looks like it's not working, the small piece of glass has been priced at £20.48 when it should be £34.13. The other question i have is how easy would it be to add/remove columns and rows should the sheet need updating?
Thanks again
Stuart

5. Ron
I could be misunderstanding something, but in your updated file, you are not applying the MinSize factor, so the first entry is under-priced. Also, shouldn't MinSize = 0.25 ?

6. Oops! The formula in F7 should be:
Code:
`=IF([@Qty]>0,MAX([@Width]*[@Height]/1000000,MinSize),0)`
(I'd misplaced the "/1000000" section)
If you simply edit the formula in F7, the table will automatcally propagate it down.

The major change I made was to convert the input area to an Excel Table...That's what's driving the "Strucured References" which display the table name and field names. I hid the table heading row because the headings aren't particularly pretty. I'd encourage you to study up on Excel Tables.

Let me know if you have more questions.

7. Revilo99
Working with Ron's first attachment, on the Splashback Data sheet, if you amend the constant in F2 to 0.25 (in place of 0.256) the price for the first entry in the price schedule will be correct. I'm not sure why the minimum size calculation has been taken out in the second attachment.

HTH

8. Hi Ron

Thank you, and thanks for all your help.

Regards

Stuart

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•