Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: Calculations on a bunch of fields

    Hi,

    I'm putting together an invoice template. I'm having a little trouble with the taxes. I based my template on the Sales Invoice that comes with Excel XP, and in it, you can insert 2 tax %. In the cells, this calculates the amount of tax from the subtotal. Unfortunately, the way taxes are calculated here, i need the second tax to be added on top of the first one.

    The first tax is 7% and the second is 7.5%. They have to be applied in this order as well. I see that this amount to calculating a lot of cells with the results of the formulas from other cells.

    Any help?

    Thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Can you perhaps attach a zip file that shows what you mean and what you want? Sometimes several formulas can be condensed into one.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    here is the file i'm working on. Here is also what the different cells should be:

    SubTotal: sum of the cells above it, up to Total
    7% : 7% of Subtotal
    7.5% : 7.5% of (result of the cell above it + Subtotal )
    Total : Subtotal+Shipping+7%+7.5%
    Amount Applied: Amount the customer wants to subtract from Total
    Amount Received: Amount given by the customer
    Change : Amount received - Amount applied
    Balance: Total - Amount Applied

    As you can see, i've got things to work up to Total, but i'm not sure i'm doing this the best way there is...

    Also, if someone could tell me how to get rid of the Popups that appear when clicking on some cells. I really dont know where to look for this!!!

    thanks for the help!
    Attached Files Attached Files
    Last edited by MrCrud; 01-23-04 at 11:44.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Haven't gone through it all but noticed this. In cell M39 you have this formula:

    =IF(L39<>"",ROUND(L39*($M$38+$M$36),2))

    However, in M38 you included an AND in the initial statement. You might want to include the same kind of AND in M39. To check it, enter $0.00 in L19, and you will discover the problem. Just change the IF statement in M39 so that it resembles M38.

    Also, format cell M41 to $.

    The pop-up (i.e. D38) is a comment added through Data Validation. Go to Data > Validation, and then the middle tab "Input Message". If you want to get rid of it, then uncheck the box "Show input message when cell is selected". Just a note: if you don't show the message, you do want the user to know that there is something special about the cell, perhaps through coloring.

    Not sure about the reason for Amount Applied vs. Amount Recevied. Wouldn't that be the same?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    Hi,

    The formulas in place are not all mine. I based my document on a template which had most of them set already.

    Ideally, the simpler the formulae, the better. The only thing that is nice, is that these formulas dont show errors.

    I fixed the error you noticed with this formula:

    =IF(AND(L38<>"",$M$36<>""),ROUND(L39*($M$38+$M$36) ,2),"")

    I think it boils down to this: In a formula, can i use the results of another formula by entering the cell of this formula, or do i have to type the whole initial formula into the second one?

    If i can do this, then this is quite simpler than i originally though, otherwise the formulas can get quite long!!!

    No more popups! thanks!! cant believe how simple that was!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    It can work either way. Longer formulas can sometimes be more efficient (time-wise), but keeping the formulas simple (i.e. referencing other cells with other formulas) makes trouble-shooting much easier.

    On a relatively simple worksheet like this I would probably stay with the simplified formulas as you have done. Other times when I have the entire page covered with formulas it is more efficient to combine formulas into one cell.

    (BTW, with more complicated the formulas and setup, I have gradually developed the practice of keeping a sheet [usually hidden] in which I detail what each formula does and why. What seems obvious to you now may cause memory lapse in 12 months - consider advice from an older guy)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Nov 2003
    Posts
    150
    Thanks a lot for the good advice. I'm keeping very good records of EVERYTHING as i go along. I'm used to work in Access and it's very useful to do things this way. I'm also writting up an entire Help file for the staff, in case i'm not there, and they're unsure about something... ( just can't trust clerks..)

    Thanks again!!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

Posting Permissions

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