# Thread: Calculations on a bunch of fields

1. Registered User
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!

2. Registered User
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.

3. Registered User
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!
Last edited by MrCrud; 01-23-04 at 10:44.

4. Registered User
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?

5. Registered User
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!

6. Registered User
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)

7. Registered User
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!!

#### Posting Permissions

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