Adding and subtracting from either column with tax or column without tax?

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

StargateFan

1) I still can't wrap my brain around the problem of Add this to the
column WITH this or to the column WITHOUT this. In this case, one
column has the invoice amount billed with tax and the other column
will have it without tax.

In other words, column C is the amount billed WITH a 6% tax we have in
Canada, and column D is WITHOUT that tax.

Column E is the total amount billed to date which would take either
the C column's value for that row or the D column, whichever is
filled.

It's how to deal with the formula in column E that is my problem.

--------------
As a specific example, the formula I currently have in E4 is this:

=IF($C4<>"",SUM($E3+$C4),"")

But it should in actuality sum E3+E4 or E3+D4, depending on which
total needs tracking. Before this new dept. standardized, they were
doing both accidentally. It's part of our new team's job, myself
included, to fix these types of things. But we need both figures so
that I can at least determine easily what they were doing by putting
the amounts in either column to get the totals to see which match.

--------------

2) Also related to the same sheet and the same problem, how do we
restrict entry to one cell out of the 2. Either the user will put a
value in column C or column D but shouldn't put both in.

Here is what would be ideal:

User enters a value in, say, C4 then tries to enter something in D4.
A message popup would be great to tell the user that it is not
possible to enter a value in that cell since there is a value in the
other column, the NO TAX one.

And vice versa with regards to putting something in D4 when there is
already something in C4.

How could this be done? Both columns are empty of formulas since
these cells are strictly user-input ones.

------------------------------------------------
Lastly, just started this contract and they ahve Excel 2007 while I
have XL2K at home. Is it possible to make code that will work in both
environments?

From the beginning I've been having those compatibility issues but it
vital to have formulas that work in both and to save in a version
compatible with my earlier XL2K as I wish to take these speadsheets
with me when I leave. I'm doing this since it's taking too long to
figure things out manually and this spreadsheet fix is for my own
personal use. So I'll need to keep them in this earlier version for
now.

Thank you! Once again, where would I be without these ngs!!!!! :oD
 
1) I still can't wrap my brain around the problem of Add this to the
column WITH this or to the column WITHOUT this. In this case, one
column has the invoice amount billed with tax and the other column
will have it without tax.
[snip]
Click to expand...

[snip]

Woops, sorry. During an OS wipe/reintall weekend before last, I tried
Office 2003 again and changed my mind and decided to keep with the
newer suite this time around. I forgot that I'd gone back to my
upgrade for a second chance, what with all the stuff that's been
happening, i.e., Windows crash, new job ... etc., etc. <g>.

Thanks!
 
In E3 I have the value 8
In C4 I have the value 4
In E4 I used =IF(C4>0,C4+E3,D4+E3) to get 12
If I delete C4 and put 6 in D4, I get 14 in E4
Note that Excel would be just as happy with =IF(C4,C4+E3,D4+E3) since a
value greater than zero is taken as TRUE in an IF condition

It is not clear from the question if only C4 or D4 will have a value. If you
never have two values in a row why not use =E3+C4+D4 since a zero in either
C4 or D4 will not change the result.

Or since C4 must always be bigger than D4 (C4 has the tax added --- in Nova
Scotia we pay 13% !!)
then =E3+MAX(C4:D4) will work

Finally: with the exception of some new functions like AVERAGEIF and SUMIFS
in Excel 2007, the way you write formulas is the same in both versions.

best wishes
 
In E3 I have the value 8
In C4 I have the value 4
In E4 I used =IF(C4>0,C4+E3,D4+E3) to get 12
If I delete C4 and put 6 in D4, I get 14 in E4
Note that Excel would be just as happy with =IF(C4,C4+E3,D4+E3) since a
value greater than zero is taken as TRUE in an IF condition

It is not clear from the question if only C4 or D4 will have a value. If you
never have two values in a row why not use =E3+C4+D4 since a zero in either
C4 or D4 will not change the result.
Click to expand...

Ah, that's the trick! Yes, it must be either/or but not both. I did
explain that, however. But I guess I did a really poor job of it

So the total must either be an addition with the tax or an addition
without the tax. So part 2 was how to bring up an error box to the
user ...:

Or since C4 must always be bigger than D4 (C4 has the tax added --- in Nova
Scotia we pay 13% !!)
Click to expand...

We do, too!! A fact many might not realize, the federal government
does not pay provincial sales tax, which is what the rest of us little
people do! This workbook is to take care of what we're billed _with_
and _without_ the GST only, so that's why just one tax.

But since previous employees in my team made a mistake, some of the
temp agency bills have been incorrectly tracked by the amounts _with_
GST. We can't change last year, but this fiscal year is new enough
that I can nip this problem in the bud!

And this spreadsheet will help. By making these changes re
restricting either one column or the other, I'll be able to determine
what changes to make in file. One column tracks billed amount, to
date (addition). The other deducts from the total "commitment", as
it's called in the GofC (subtraction).

And all I'll need to do is input both totals (woops ..., will return
to that) and then input the billing amounts. Comparing the totals
between spreadsheet and Expenditure Tracking form in each file, will
tell me what they did. Believe it or not, the forum doesn't guide
users on which amount to track. I'll be fixing that, too!!!

Just realized that I'll have to factor in both totals so that the
formula in the columns total not only calculates appropriately
depending on the column, but it will also have to the total into
account as well.

Thanks! :oD
 
Back
Top