Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108783 > unrolled thread

req. for ideas for numeric worksheet

Back to article view | Back to microsoft.public.excel.programming

Contents

  req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-22 16:43 -0700
    Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 00:41 -0400
      Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 00:18 -0700
        Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 11:46 -0400
          Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 13:49 -0700
            Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 17:04 -0400
              Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 14:15 -0700
            Re: req. for ideas for numeric worksheet Claus Busch <claus_busch@t-online.de> - 2016-04-23 23:33 +0200
              Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 14:43 -0700
                Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 18:32 -0400

#108783 — 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

[toc] | [next] | [standalone]

#108784

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

[toc] | [prev] | [next] | [standalone]

#108785

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

[toc] | [prev] | [next] | [standalone]

#108786

> 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

[toc] | [prev] | [next] | [standalone]

#108787

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.

[toc] | [prev] | [next] | [standalone]

#108788

> 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

[toc] | [prev] | [next] | [standalone]

#108789

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.

[toc] | [prev] | [next] | [standalone]

#108790

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

[toc] | [prev] | [next] | [standalone]

#108791

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

[toc] | [prev] | [next] | [standalone]

#108792

> 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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web