Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding only numeric values in a worksheet using vb.net? | Excel Programming | |||
Loss Run on Excel worksheet......Any Ideas?? | Excel Worksheet Functions | |||
How do I match numeric coding in a worksheet to words in a pdf fil | Excel Discussion (Misc queries) | |||
How to compare two numeric column in same worksheet | Excel Worksheet Functions | |||
Merging worksheet - ideas | Excel Programming |