Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default req. for ideas for numeric worksheet

I have a tax worksheet that is being used to show the assessed tax on
housing units for '12-'15, including any amount paid for each of those
years. Right now I have 8 columns of numeric data: assessed tax and paid
tax for each of the 4 years. I have two questions about how to improve
on this due to a complication. Some addresses have two rows, one for an
owner who sold and one for the purchasing owner, when a unit was sold
and purchased in a tax fiscal year. Some owners will have 0s as the
assessed tax due for any years following the sales year as they were no
lonnger the owner.

The worksheet currently has conditional formatting setting alternating
rows to white or a light gray background color, and all of the columns
are set to numeric.

The person who is working with the worksheet asked if it would be
possible to use white text on a dark grey or black background to format
the "0" entries for the years when an original owner no longer owned the
property, e.g if Bob A. owned unit 3 in 2012, then sold it in 2013, he
would ow $0 of the assessed taxes for 2014 and 2015.

What is the preferred accounting approach to something like this, so the
person working with the worksheet can quickly see that taxes in
subsequent years after a sale are not billed to the seller? Is the
approach requested by the worker a good one (dark cell background
colors) or is there a better way? I have zero bookkeeping or accounting
training or experience. Should I put an "n/a" or "n/r" (not resident) in
the cells, use a white zero on a dark background, use "---", or
something else? If it was non-numeric I would have to change the cells
to general or text, then check for numeric entry or "n/?" when doing the
calculations.

Thanks for your thoughts!
Mike
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default req. for ideas for numeric worksheet

Mike,
Typically.., some sort of 'code' or 'flag' is used to show current
status. What these are is entirely user specific such as your
suggestion to use "n/r", for example. I'd avoid using a slash
character, though, as your example "n/a" is an intrinsic error flag.

You don't need to be consistent in the length of the status code IMO as
it's more benefit it be descriptive in context to its usage...

T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default req. for ideas for numeric worksheet

On 4/22/2016 9:41 PM, GS wrote:
Mike,
Typically.., some sort of 'code' or 'flag' is used to show current
status. What these are is entirely user specific such as your suggestion
to use "n/r", for example. I'd avoid using a slash character, though, as
your example "n/a" is an intrinsic error flag.
You don't need to be consistent in the length of the status code IMO as
it's more benefit it be descriptive in context to its usage...
T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on
HTH

Thanks GS,

Are there any tricks for handling these characters instead of numeric
input? Is my best approach to make all of the cells TEXT and then
process them individually looking for either numeric or specific coded
entries (like the ones you listed) when testing for legal inputs? Also
is there a way to have the cells change fill and text color when certain
strings are entered into them? I'm still learning Excel and wondering
how to make this sheet easy to use.
Thanks again,
Mike
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default req. for ideas for numeric worksheet

On 4/22/2016 9:41 PM, GS wrote:
Mike,
Typically.., some sort of 'code' or 'flag' is used to show current
status. What these are is entirely user specific such as your
suggestion
to use "n/r", for example. I'd avoid using a slash character,
though, as
your example "n/a" is an intrinsic error flag.
You don't need to be consistent in the length of the status code
IMO as
it's more benefit it be descriptive in context to its usage...
T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on
HTH

Thanks GS,

Are there any tricks for handling these characters instead of numeric
input? Is my best approach to make all of the cells TEXT and then
process them individually looking for either numeric or specific
coded entries (like the ones you listed) when testing for legal
inputs? Also is there a way to have the cells change fill and text
color when certain strings are entered into them? I'm still learning
Excel and wondering how to make this sheet easy to use.
Thanks again,
Mike


Normally, these codes would have their own dedicated column so every
transaction gets a status flag. Depending on how many flags, CF could
be a challenge if allocated to just 1 target cell in 1 column. Your
calculation formulas can be dependant on status codes, obviating need
for user intervention. Hard to advise without seeing a sample of what
you're working with that also shows/notes what expectations you have
for how it should work.

FWIW
I made a rental units manager for the landlord of the commercial space
I was renting some years back. This handled tenants (instead of owners)
and how rents were charged according to the various criteria of this
landlord based on tenant usage of the rented space. This project is
generic in structure (ergo expandable core app) so it can be 'tailored'
to user-specific needs. This landlord also wanted it to include
invoicing and notification printouts for selected/all tenants. I
suspect a housing units management project shouldn't be much different
in that your project handles taxes instead of rents! Perhaps if I can
see what you're trying to do I can better assist...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default req. for ideas for numeric worksheet

On 4/23/2016 8:46 AM, GS wrote:
On 4/22/2016 9:41 PM, GS wrote:
Mike,
Typically.., some sort of 'code' or 'flag' is used to show current
status. What these are is entirely user specific such as your suggestion
to use "n/r", for example. I'd avoid using a slash character, though, as
your example "n/a" is an intrinsic error flag.
You don't need to be consistent in the length of the status code IMO as
it's more benefit it be descriptive in context to its usage...
T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on
HTH

Thanks GS,

Are there any tricks for handling these characters instead of numeric
input? Is my best approach to make all of the cells TEXT and then
process them individually looking for either numeric or specific coded
entries (like the ones you listed) when testing for legal inputs? Also
is there a way to have the cells change fill and text color when
certain strings are entered into them? I'm still learning Excel and
wondering how to make this sheet easy to use.
Thanks again,
Mike


Normally, these codes would have their own dedicated column so every
transaction gets a status flag. Depending on how many flags, CF could be
a challenge if allocated to just 1 target cell in 1 column. Your
calculation formulas can be dependant on status codes, obviating need
for user intervention. Hard to advise without seeing a sample of what
you're working with that also shows/notes what expectations you have for
how it should work.

FWIW
I made a rental units manager for the landlord of the commercial space I
was renting some years back. This handled tenants (instead of owners)
and how rents were charged according to the various criteria of this
landlord based on tenant usage of the rented space. This project is
generic in structure (ergo expandable core app) so it can be 'tailored'
to user-specific needs. This landlord also wanted it to include
invoicing and notification printouts for selected/all tenants. I suspect
a housing units management project shouldn't be much different in that
your project handles taxes instead of rents! Perhaps if I can see what
you're trying to do I can better assist...


Right now I think you have given me great advice and I think I can get
this to work, but I have one more question, if it's not clear I can
email you the workbook. If I change the types of cells to text for some
of the data columns, e.g. using the flag "EX" (exempt), I would need to
change the equation that calculates a value for the balance column. Is
it possible to write a function that will add/subtract existing numeric
entries but ignore non-numeric entries? I.e. Right now my balance
calculation looks like this (all cells are numeric):

=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)

If I change L, K, O, N, R, Q to text is there a way to add just I-H to
the balance and ignore text entries that I can paste into the balance
column formula?

Thank you for spending so much time on this, I appreciate it.




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default req. for ideas for numeric worksheet

On 4/23/2016 8:46 AM, GS wrote:
On 4/22/2016 9:41 PM, GS wrote:
Mike,
Typically.., some sort of 'code' or 'flag' is used to show
current
status. What these are is entirely user specific such as your
suggestion
to use "n/r", for example. I'd avoid using a slash character,
though, as
your example "n/a" is an intrinsic error flag.
You don't need to be consistent in the length of the status code
IMO as
it's more benefit it be descriptive in context to its usage...
T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on
HTH

Thanks GS,

Are there any tricks for handling these characters instead of
numeric
input? Is my best approach to make all of the cells TEXT and then
process them individually looking for either numeric or specific
coded
entries (like the ones you listed) when testing for legal inputs?
Also
is there a way to have the cells change fill and text color when
certain strings are entered into them? I'm still learning Excel
and
wondering how to make this sheet easy to use.
Thanks again,
Mike


Normally, these codes would have their own dedicated column so
every
transaction gets a status flag. Depending on how many flags, CF
could be
a challenge if allocated to just 1 target cell in 1 column. Your
calculation formulas can be dependant on status codes, obviating
need
for user intervention. Hard to advise without seeing a sample of
what
you're working with that also shows/notes what expectations you
have for
how it should work.

FWIW
I made a rental units manager for the landlord of the commercial
space I
was renting some years back. This handled tenants (instead of
owners)
and how rents were charged according to the various criteria of
this
landlord based on tenant usage of the rented space. This project is
generic in structure (ergo expandable core app) so it can be
'tailored'
to user-specific needs. This landlord also wanted it to include
invoicing and notification printouts for selected/all tenants. I
suspect
a housing units management project shouldn't be much different in
that
your project handles taxes instead of rents! Perhaps if I can see
what
you're trying to do I can better assist...


Right now I think you have given me great advice and I think I can
get this to work, but I have one more question, if it's not clear I
can email you the workbook. If I change the types of cells to text
for some of the data columns, e.g. using the flag "EX" (exempt), I
would need to change the equation that calculates a value for the
balance column. Is it possible to write a function that will
add/subtract existing numeric entries but ignore non-numeric entries?
I.e. Right now my balance calculation looks like this (all cells are
numeric):

=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)

If I change L, K, O, N, R, Q to text is there a way to add just I-H
to the balance and ignore text entries that I can paste into the
balance column formula?

Thank you for spending so much time on this, I appreciate it.


I wouldn't 'set' cells to any specific 'data type' unless data type
formatting is required. Leave them as 'General' otherwise. Excel will
ignore non-numeric cells for most formulas so it's okay to sum a range
that also includes text. In fact, there are advantages to doing this if
the numeric data will change by adding/deleting rows/cols within a
range being summed!

Safest way to pass a file here is to post a link to an online storage
space like dropbox or the like. If you want to continue contact via
email then make sure your file has your email address in it. I'm happy
to look at your file and see where I can be of further assistance.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default req. for ideas for numeric worksheet

On 4/23/2016 2:04 PM, GS wrote:
On 4/23/2016 8:46 AM, GS wrote:
On 4/22/2016 9:41 PM, GS wrote:
Mike,
Typically.., some sort of 'code' or 'flag' is used to show current
status. What these are is entirely user specific such as your
suggestion
to use "n/r", for example. I'd avoid using a slash character,
though, as
your example "n/a" is an intrinsic error flag.
You don't need to be consistent in the length of the status code
IMO as
it's more benefit it be descriptive in context to its usage...
T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on
HTH

Thanks GS,

Are there any tricks for handling these characters instead of numeric
input? Is my best approach to make all of the cells TEXT and then
process them individually looking for either numeric or specific coded
entries (like the ones you listed) when testing for legal inputs? Also
is there a way to have the cells change fill and text color when
certain strings are entered into them? I'm still learning Excel and
wondering how to make this sheet easy to use.
Thanks again,
Mike

Normally, these codes would have their own dedicated column so every
transaction gets a status flag. Depending on how many flags, CF could be
a challenge if allocated to just 1 target cell in 1 column. Your
calculation formulas can be dependant on status codes, obviating need
for user intervention. Hard to advise without seeing a sample of what
you're working with that also shows/notes what expectations you have for
how it should work.

FWIW
I made a rental units manager for the landlord of the commercial space I
was renting some years back. This handled tenants (instead of owners)
and how rents were charged according to the various criteria of this
landlord based on tenant usage of the rented space. This project is
generic in structure (ergo expandable core app) so it can be 'tailored'
to user-specific needs. This landlord also wanted it to include
invoicing and notification printouts for selected/all tenants. I suspect
a housing units management project shouldn't be much different in that
your project handles taxes instead of rents! Perhaps if I can see what
you're trying to do I can better assist...


Right now I think you have given me great advice and I think I can get
this to work, but I have one more question, if it's not clear I can
email you the workbook. If I change the types of cells to text for
some of the data columns, e.g. using the flag "EX" (exempt), I would
need to change the equation that calculates a value for the balance
column. Is it possible to write a function that will add/subtract
existing numeric entries but ignore non-numeric entries? I.e. Right
now my balance calculation looks like this (all cells are numeric):

=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)

If I change L, K, O, N, R, Q to text is there a way to add just I-H to
the balance and ignore text entries that I can paste into the balance
column formula?

Thank you for spending so much time on this, I appreciate it.


I wouldn't 'set' cells to any specific 'data type' unless data type
formatting is required. Leave them as 'General' otherwise. Excel will
ignore non-numeric cells for most formulas so it's okay to sum a range
that also includes text. In fact, there are advantages to doing this if
the numeric data will change by adding/deleting rows/cols within a range
being summed!

Safest way to pass a file here is to post a link to an online storage
space like dropbox or the like. If you want to continue contact via
email then make sure your file has your email address in it. I'm happy
to look at your file and see where I can be of further assistance.


Thanks! I'll give that a try, if I run into problems I'll post a dropbox
link.

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default req. for ideas for numeric worksheet

Hi Mike,

Am Sat, 23 Apr 2016 13:49:23 -0700 schrieb Mike S:

=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)

If I change L, K, O, N, R, Q to text is there a way to add just I-H to
the balance and ignore text entries that I can paste into the balance
column formula?


the function SUM ignores text.
Try:
=SUM(IF(MOD(COLUMN(H9:R9),3)=0,H9:R9))-SUM(IF(MOD(COLUMN(H9:R9),3)=2,H9:R9))
and insert the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default req. for ideas for numeric worksheet

On 4/23/2016 2:33 PM, Claus Busch wrote:
Hi Mike,
Am Sat, 23 Apr 2016 13:49:23 -0700 schrieb Mike S:


=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)
If I change L, K, O, N, R, Q to text is there a way to add just I-H to
the balance and ignore text entries that I can paste into the balance
column formula?


the function SUM ignores text.
Try:
=SUM(IF(MOD(COLUMN(H9:R9),3)=0,H9:R9))-SUM(IF(MOD(COLUMN(H9:R9),3)=2,H9:R9))
and insert the formula with CTRL+Shift+Enter
Regards
Claus B.


GS and Claus,

Changing the numeric entry columns to general, entering "EX" where
needed, then using SUM worked perfectly. Sum ignores non-numric as
advertised and does the math correctly... no more #VALUE cells shouting
at me from the page! I had no idea SUM worked that way.

Thank you very much!
Mike

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default req. for ideas for numeric worksheet

On 4/23/2016 2:33 PM, Claus Busch wrote:
Hi Mike,
Am Sat, 23 Apr 2016 13:49:23 -0700 schrieb Mike S:


=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)
If I change L, K, O, N, R, Q to text is there a way to add just
I-H to
the balance and ignore text entries that I can paste into the
balance
column formula?


the function SUM ignores text.
Try:
=SUM(IF(MOD(COLUMN(H9:R9),3)=0,H9:R9))-SUM(IF(MOD(COLUMN(H9:R9),3)=2,H9:R9))
and insert the formula with CTRL+Shift+Enter
Regards
Claus B.


GS and Claus,

Changing the numeric entry columns to general, entering "EX" where
needed, then using SUM worked perfectly. Sum ignores non-numric as
advertised and does the math correctly... no more #VALUE cells
shouting at me from the page! I had no idea SUM worked that way.

Thank you very much!
Mike


Glad to be some help...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
finding only numeric values in a worksheet using vb.net? Andrius B. Excel Programming 6 December 23rd 07 10:25 PM
Loss Run on Excel worksheet......Any Ideas?? AK226 Excel Worksheet Functions 6 November 1st 07 06:20 PM
How do I match numeric coding in a worksheet to words in a pdf fil Motown Mick Excel Discussion (Misc queries) 5 June 20th 07 01:06 AM
How to compare two numeric column in same worksheet homefunwork.com Excel Worksheet Functions 5 February 2nd 07 05:17 PM
Merging worksheet - ideas mortals Excel Programming 0 December 8th 05 08:41 PM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"