Results 1 to 8 of 8

Thread: Setting a minimum area charge

  1. #1
    Neophyte Revilo99's Avatar
    Join Date
    Mar 2019
    Posts
    3
    Articles
    0
    Excel Version
    office 2019

    Setting a minimum area charge



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    100
    Articles
    0
    Excel Version
    2013, 2016, O365
    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.
    Attached Files Attached Files
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    100
    Articles
    0
    Excel Version
    2013, 2016, O365
    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.
    Attached Files Attached Files
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  4. #4
    Neophyte Revilo99's Avatar
    Join Date
    Mar 2019
    Posts
    3
    Articles
    0
    Excel Version
    office 2019
    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. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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. #6
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    100
    Articles
    0
    Excel Version
    2013, 2016, O365
    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.
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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. #8
    Neophyte Revilo99's Avatar
    Join Date
    Mar 2019
    Posts
    3
    Articles
    0
    Excel Version
    office 2019
    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
  •