Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Formula with database

    I have attached a spreadsheet containing a contract that I am needing some help with. I need to know how to add 10% to the price in the M21 cell on the "Contract" sheet, plus the formula to add the sales tax, as in cell C33. This formula would go in the J20 and J24 cells. I tried figuring it out from how it was done in cell C33 but I just don't know enough about formulas to do it. Thanks again for your help.
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Y32rem, Just having a look at your spreadsheet. I've done something like this before One suggestion in your 2013 Mailing tab, instead of putting the column numbers in row A suggest you try =COLUMN() and in your vlookup, refer to the respective cell, so =VLOOKUP(Cust,Database,16) would become =VLOOKUP(Cust,Database,'2013 Mailing'!P1) and so on. This way if you decide that you need to insert a new column you don't need to readjust the col index number. Another suggestion is to put a "spinner" out to the site on Residential(2) tab and in the Format control Cell link to $B$10 using the spinner the data will "Roll around" as you click on the spinner up or down.

    I know that doesn't address your question, but I'll have a look at that later, but just a tip to make things easier and more flexible.

    Regards

    Verada

  3. #3
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi
    I really like Verada's idea of using =column() rather than putting column numbers but since you "don't know much about formulas" I fear that it might be no or little use to you.
    Correct me if Im wrong, you inherited the spreadsheet and now trying to work out how it does what it does and adjust a thing or two?

    I need to know how to add 10% to the price in the M21 cell on the "Contract" sheet - is this is a permanent change? Or you just want an option to add 10% as you please?
    Thinking about the second part of your question. Can you upload another spreadsheet with results you wish to achieve? Just type figures you want to get where you want them to appear and highlight them, just so I am certain that I know what you're talking about.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Formula with a database

    Okay, I have attached the sample contract again after adding "Residential3" with your requested additions so you can see what the correct figures would be. I forgot I had added another column for Senior Pricing. The way that works for cells K20 and K24 is - Cell M21 X 10% X .95 (for the 5% discount) X 8% (tax). Sorry I forgot to mention that in my last post.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Do you need to show different (increased by 10%) figures anywhere else? C33 or J33 perhaps? if not here is the spreadsheet with some simple formulas in indicated cells. Let me know if you need anything more sophisticated. Happy to help.Copy of Sample contract.xls
    Last edited by jckplck; 2013-01-30 at 13:53.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    No, the cells you referenced are affected by the changes I need. And the only change I may need to make going forward would be in the percentage of increase on cell M21. I'll check out what you've done and report back. Thanks!

  7. #7
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    No, the cells you referenced are affected by the changes I need. And the only change I may need to make going forward would be in the percentage of increase on cell M21. I'll check out what you've done and report back. Thanks!

  8. #8
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Did you ever have one of those "duh" moments? I was trying to make this difficult and you made it so easy. That works perfectly and so simple. That's what has made all of you at Woody's Lounge so invaluable to me over the past 12 years! Thanks so much!

  9. #9
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts

    happy to help

  10. #10
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    I have again attached the sample contract from this thread. Is there a way to round the figure in cells J20 and J24 to the nearest dollar for tax exempt organizations? I tried figuring it out using "File, insert function" but am clueless how to do it. Thanks!
    Attached Files Attached Files

  11. #11
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    =ROUND(J22*1.1,0)
    =ROUND(J26*1.1,0)

    and format your cells to show no decimal places

    you can also use ROUNDUP(rounds number away from zero) or ROUNDDOWN (rounds number towards zero) in this syntax.

  12. #12
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Thanks again so much. I seem to try and make everything difficult and you make it look so easy! Again, thank you for being a life saver!

Posting Permissions

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