See cell O279...trying to figure out taxes for individual line items from an invoice where we are proportionately applying discounts to taxable items. Thought I had it, but end up with DIV/0 error. Might be a problem with my parentheses.
See cell O279...trying to figure out taxes for individual line items from an invoice where we are proportionately applying discounts to taxable items. Thought I had it, but end up with DIV/0 error. Might be a problem with my parentheses.
I don't understand the OR condition in your SUMIF: it will always result in zero and you are comparing numbers with a TRUE/FALSE condition.
Trying to use the OR condition to apply calculations only to those rows which belong to a particular invoice. Thought I could use the OR condition to allow the same formula to be used in that entire column since it would be looking only at the same invoice if the invoice number was equal to the one in the previous row or equal to the one just following it. As long as the invoice number was equal to one or the other, it would know it was pulling numbers into the calculation from the same invoice. Hope that makes sense. Perhaps I can't use the OR condition to achieve that. If you know of a better way which requires no manual determination on the part of the person who is doing data entry, I'm open to the suggestion.
If we take the Invoice Number in A279, what should the calculation in O279 be?
There needs to be test for the Invoice number but I need to understand the SUMIF part of the calculation.
Try this in O279 and copy down
=IF(N279<>"x",(J279-((J279/(SUMIF(A279:A307,A279,J279:J307)))*(SUMIF(A279:A307,A279,L279:L307))))*0.0825,0)
Can we apply this formula from O4 down (NO! having tried it!)or can there be a generic formula to meet all conditions?
You have to be able to have Excel only include in the calculations the rows which have the same invoice #. I believe the criteria element of the above SUMIF will always just look to A279 rather than looking to A280 to see if it equals A279. It won't move down A280, A281, A282 etc until it hits the row in which the invoice # changes. I need it to generically search for the same invoice #, note the rows that have that invoice # in common, then look to perform the calculation only using these rows.
It will only perform the calculation when the invoice number matches.
J279 changes in SUMIF as you drag the formula down.
Modified formula to fix range:
=IF(N279<>"x",(J279-((J279/(SUMIF($A$279:$A$307,A279,$J$279:$J$307)))*(SUMIF($A$279:$A$307,A279,$L$279:$L$307))))*0.0825,0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks