Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Cell Formatting
I set up a custom cell format to bring forward (1.) the sum a cell, which is
a DATE and (2.) the sum of a different cell, which is a %. However, the program is taking the luxury of converting the -% data to a positive % number. Why is this, and how can I correct it? The Custom cell format is as follows: m/dd/yy;0.00% Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Cell Formatting
I still wondering how you're getting *any other* display besides a date
format! I can't duplicate your results with the custom format you posted. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Tom" wrote in message ... I set up a custom cell format to bring forward (1.) the sum a cell, which is a DATE and (2.) the sum of a different cell, which is a %. However, the program is taking the luxury of converting the -% data to a positive % number. Why is this, and how can I correct it? The Custom cell format is as follows: m/dd/yy;0.00% Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Cell Formatting
The information that is being brought forward is coming from one cell with a
"LOOKUP" formula in it (the DATE field) and the other cell has an "OFFSET" formula (the % field) in it. I did make a mistake in my displayed cell format, it should read m/d/yy not m/dd/yy. I appreciate your attempt to assist in this matter. Tom "RagDyeR" wrote: I still wondering how you're getting *any other* display besides a date format! I can't duplicate your results with the custom format you posted. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Tom" wrote in message ... I set up a custom cell format to bring forward (1.) the sum a cell, which is a DATE and (2.) the sum of a different cell, which is a %. However, the program is taking the luxury of converting the -% data to a positive % number. Why is this, and how can I correct it? The Custom cell format is as follows: m/dd/yy;0.00% Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Cell Formatting
RagDyeR:
When formatting a cell, it doesn't matter where the data came from (Vlookup, Offset, or any other formula). It only matters what the resulting data is. I'm also having a hard time understanding why you would want a date *and* a percentage in the same cell, but regardless, here are some pointers. Excel is displaying your negative percentages without a sign because you told it to. The format m/dd/yy;0.00% means "if the number in the cell is positive, use a format of m/dd/yy; it it's negative, use 0.00%". The semicolon is what separates the format for positive numbers from negative numbers. It's most commonly used to display negative numbers in red. In your case, you want something different. If the number is a percentage, you want the 0.00% format. If it's a date, you want the date format. You now have to tell Excel how to tell the difference. Fortunately, percentages are very low numbers, and dates are much higher numbers. Depending on the highest percentage you could ever have, you could, for example, tell Excel to format everything less than 10 (ie, 1000%) as a percentage, and everything else as a date, with the following: [<10]0.00%;m/dd/yy -- Regards, Fred "Tom" wrote in message ... The information that is being brought forward is coming from one cell with a "LOOKUP" formula in it (the DATE field) and the other cell has an "OFFSET" formula (the % field) in it. I did make a mistake in my displayed cell format, it should read m/d/yy not m/dd/yy. I appreciate your attempt to assist in this matter. Tom "RagDyeR" wrote: I still wondering how you're getting *any other* display besides a date format! I can't duplicate your results with the custom format you posted. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Tom" wrote in message ... I set up a custom cell format to bring forward (1.) the sum a cell, which is a DATE and (2.) the sum of a different cell, which is a %. However, the program is taking the luxury of converting the -% data to a positive % number. Why is this, and how can I correct it? The Custom cell format is as follows: m/dd/yy;0.00% Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Cell Formatting
BINGO - That worked pefectly.
Thank you, so very much for your help!!!! Tom "Fred Smith" wrote: RagDyeR: When formatting a cell, it doesn't matter where the data came from (Vlookup, Offset, or any other formula). It only matters what the resulting data is. I'm also having a hard time understanding why you would want a date *and* a percentage in the same cell, but regardless, here are some pointers. Excel is displaying your negative percentages without a sign because you told it to. The format m/dd/yy;0.00% means "if the number in the cell is positive, use a format of m/dd/yy; it it's negative, use 0.00%". The semicolon is what separates the format for positive numbers from negative numbers. It's most commonly used to display negative numbers in red. In your case, you want something different. If the number is a percentage, you want the 0.00% format. If it's a date, you want the date format. You now have to tell Excel how to tell the difference. Fortunately, percentages are very low numbers, and dates are much higher numbers. Depending on the highest percentage you could ever have, you could, for example, tell Excel to format everything less than 10 (ie, 1000%) as a percentage, and everything else as a date, with the following: [<10]0.00%;m/dd/yy -- Regards, Fred "Tom" wrote in message ... The information that is being brought forward is coming from one cell with a "LOOKUP" formula in it (the DATE field) and the other cell has an "OFFSET" formula (the % field) in it. I did make a mistake in my displayed cell format, it should read m/d/yy not m/dd/yy. I appreciate your attempt to assist in this matter. Tom "RagDyeR" wrote: I still wondering how you're getting *any other* display besides a date format! I can't duplicate your results with the custom format you posted. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Tom" wrote in message ... I set up a custom cell format to bring forward (1.) the sum a cell, which is a DATE and (2.) the sum of a different cell, which is a %. However, the program is taking the luxury of converting the -% data to a positive % number. Why is this, and how can I correct it? The Custom cell format is as follows: m/dd/yy;0.00% Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Custom Formatting | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Refresh PivotTable, lose custom cell formatting | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Custom formatting for a cell | Excel Discussion (Misc queries) |