| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking-up Columns w/calc'd Values ONLY to Calculate Average
Your help will be appreciated. PROBLEM: The formula below returns an answer
of 100% (WHICH ISN'T RIGHT). The correct result should be 11%. =IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22)) WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW): 1) LOOKUP CALCULATED VALUES IN F18:M18 I.E. F18:I18 2) LOOKUP CALCULATED VALUES IN F22:M22 I.E. F22:I22 3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM INCREMENTING AVERAGE OF F18:M18 ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 50% 33% -25% 50% 0% 0% 0% 0% 19 4 6 8 6 9 20 21 5 4 2 6 2 22 -20%-50% 200% -67% 0% 0% 0% 0% Thank-you - Sony -- Sony Luvy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking-up Columns w/calc'd Values ONLY to Calculate Average
Hi!
So you want to exclude 0 values. The 0's at the end of the range are probably due to the row below not having a value entered. What if you have this: ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 0% 100% -25% 50% 0% 0% 0% 0% 19 4 4 8 6 9 Do want to exclude the first 0 also? Biff "sony654" wrote in message ... Your help will be appreciated. PROBLEM: The formula below returns an answer of 100% (WHICH ISN'T RIGHT). The correct result should be 11%. =IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22)) WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW): 1) LOOKUP CALCULATED VALUES IN F18:M18 I.E. F18:I18 2) LOOKUP CALCULATED VALUES IN F22:M22 I.E. F22:I22 3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM INCREMENTING AVERAGE OF F18:M18 ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 50% 33% -25% 50% 0% 0% 0% 0% 19 4 6 8 6 9 20 21 5 4 2 6 2 22 -20%-50% 200% -67% 0% 0% 0% 0% Thank-you - Sony -- Sony Luvy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking-up Columns w/calc'd Values ONLY to Calculate Average
Biff - I want to write the formula so that it responds to the worksheet
layout I displayed. Bottom line the formula must calculate the average of all "non-zero" columns f18:m18, and subtract the average of all "non-zero"columns f22:m22. Any ideas? Thanks -- Sony Luvy "Biff" wrote: Hi! So you want to exclude 0 values. The 0's at the end of the range are probably due to the row below not having a value entered. What if you have this: ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 0% 100% -25% 50% 0% 0% 0% 0% 19 4 4 8 6 9 Do want to exclude the first 0 also? Biff "sony654" wrote in message ... Your help will be appreciated. PROBLEM: The formula below returns an answer of 100% (WHICH ISN'T RIGHT). The correct result should be 11%. =IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22)) WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW): 1) LOOKUP CALCULATED VALUES IN F18:M18 I.E. F18:I18 2) LOOKUP CALCULATED VALUES IN F22:M22 I.E. F22:I22 3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM INCREMENTING AVERAGE OF F18:M18 ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 50% 33% -25% 50% 0% 0% 0% 0% 19 4 6 8 6 9 20 21 5 4 2 6 2 22 -20%-50% 200% -67% 0% 0% 0% 0% Thank-you - Sony -- Sony Luvy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking-up Columns w/calc'd Values ONLY to Calculate Average
Try this:
Entered as an array using the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(F18:M18<0,F18:M18))-AVERAGE(IF(F22:M22<0,F22:M22)) Format as PERCENTAGE Biff "sony654" wrote in message ... Biff - I want to write the formula so that it responds to the worksheet layout I displayed. Bottom line the formula must calculate the average of all "non-zero" columns f18:m18, and subtract the average of all "non-zero"columns f22:m22. Any ideas? Thanks -- Sony Luvy "Biff" wrote: Hi! So you want to exclude 0 values. The 0's at the end of the range are probably due to the row below not having a value entered. What if you have this: ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 0% 100% -25% 50% 0% 0% 0% 0% 19 4 4 8 6 9 Do want to exclude the first 0 also? Biff "sony654" wrote in message ... Your help will be appreciated. PROBLEM: The formula below returns an answer of 100% (WHICH ISN'T RIGHT). The correct result should be 11%. =IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22)) WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW): 1) LOOKUP CALCULATED VALUES IN F18:M18 I.E. F18:I18 2) LOOKUP CALCULATED VALUES IN F22:M22 I.E. F22:I22 3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM INCREMENTING AVERAGE OF F18:M18 ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 50% 33% -25% 50% 0% 0% 0% 0% 19 4 6 8 6 9 20 21 5 4 2 6 2 22 -20%-50% 200% -67% 0% 0% 0% 0% Thank-you - Sony -- Sony Luvy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking-up Columns w/calc'd Values ONLY to Calculate Average
Biff - Thanks. We are almost there. I applied the formula:
=AVERAGE(IF(F18:M18<0,F18:M18))-AVERAGE(IF(F22:M22<0,F22:M22)) but what does key combo of ctl, hift, enter mean, and how do I execute it? When the formula did work a couple of times, and didn't default to a "value" error, it did not calculate the percent difference until I entered values in column I. Calculations in 18f:18h, and 22f:22h did not trigger the formula to calculate and display a percent difference. Your thoughts? Thanks for your continued assistance. -- Sony Luvy "Biff" wrote: Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(F18:M18<0,F18:M18))-AVERAGE(IF(F22:M22<0,F22:M22)) Format as PERCENTAGE Biff "sony654" wrote in message ... Biff - I want to write the formula so that it responds to the worksheet layout I displayed. Bottom line the formula must calculate the average of all "non-zero" columns f18:m18, and subtract the average of all "non-zero"columns f22:m22. Any ideas? Thanks -- Sony Luvy "Biff" wrote: Hi! So you want to exclude 0 values. The 0's at the end of the range are probably due to the row below not having a value entered. What if you have this: ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 0% 100% -25% 50% 0% 0% 0% 0% 19 4 4 8 6 9 Do want to exclude the first 0 also? Biff "sony654" wrote in message ... Your help will be appreciated. PROBLEM: The formula below returns an answer of 100% (WHICH ISN'T RIGHT). The correct result should be 11%. =IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22)) WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW): 1) LOOKUP CALCULATED VALUES IN F18:M18 I.E. F18:I18 2) LOOKUP CALCULATED VALUES IN F22:M22 I.E. F22:I22 3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM INCREMENTING AVERAGE OF F18:M18 ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 50% 33% -25% 50% 0% 0% 0% 0% 19 4 6 8 6 9 20 21 5 4 2 6 2 22 -20%-50% 200% -67% 0% 0% 0% 0% Thank-you - Sony -- Sony Luvy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking-up Columns w/calc'd Values ONLY to Calculate Average
=AVERAGE(IF(F18:M18<0,F18:M18))-AVERAGE(IF(F22:M22<0,F22:M22))
but what does key combo of ctl, hift, enter mean, and how do I execute it? The formula is an array formula. For it to work properly you MUST use a combination of keys strokes to enter it. That combination of keys are the CTRL key, the SHIFT key and the ENTER key. Try this: Double click the cell that the formula is in. Hold down both the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly braces { }. You cannot just type these braces in. You MUST use the key combo. Also, if you ever edit an array formula it MUST be re-entered using the key combo. Biff "sony654" wrote in message ... Biff - Thanks. We are almost there. I applied the formula: =AVERAGE(IF(F18:M18<0,F18:M18))-AVERAGE(IF(F22:M22<0,F22:M22)) but what does key combo of ctl, hift, enter mean, and how do I execute it? When the formula did work a couple of times, and didn't default to a "value" error, it did not calculate the percent difference until I entered values in column I. Calculations in 18f:18h, and 22f:22h did not trigger the formula to calculate and display a percent difference. Your thoughts? Thanks for your continued assistance. -- Sony Luvy "Biff" wrote: Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(F18:M18<0,F18:M18))-AVERAGE(IF(F22:M22<0,F22:M22)) Format as PERCENTAGE Biff "sony654" wrote in message ... Biff - I want to write the formula so that it responds to the worksheet layout I displayed. Bottom line the formula must calculate the average of all "non-zero" columns f18:m18, and subtract the average of all "non-zero"columns f22:m22. Any ideas? Thanks -- Sony Luvy "Biff" wrote: Hi! So you want to exclude 0 values. The 0's at the end of the range are probably due to the row below not having a value entered. What if you have this: ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 0% 100% -25% 50% 0% 0% 0% 0% 19 4 4 8 6 9 Do want to exclude the first 0 also? Biff "sony654" wrote in message ... Your help will be appreciated. PROBLEM: The formula below returns an answer of 100% (WHICH ISN'T RIGHT). The correct result should be 11%. =IF(COUNTIF(F18:M18,"<0"),LOOKUP(2,1/(F18:M18<0)*ISNUMBER(F18:M18)),AVERAGE(F18:M18)-AVERAGE(F22:M22)) WHAT I AM TRYING TO ACCOMPLISH (LAYOUT BELOW): 1) LOOKUP CALCULATED VALUES IN F18:M18 I.E. F18:I18 2) LOOKUP CALCULATED VALUES IN F22:M22 I.E. F22:I22 3) SUBTRACT INCREMENTING AVERAGE OF F22:M22 FROM INCREMENTING AVERAGE OF F18:M18 ************WORKSHEET LAYOUT**************** COLUMN D E F G H I J K L M ROW 18 50% 33% -25% 50% 0% 0% 0% 0% 19 4 6 8 6 9 20 21 5 4 2 6 2 22 -20%-50% 200% -67% 0% 0% 0% 0% Thank-you - Sony -- Sony Luvy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting values from columns into one cell | Excel Discussion (Misc queries) | |||
| How to calculate average from large dataset in several sheets? | Excel Worksheet Functions | |||
| How do I match identical values in 2 columns and then sort? | Excel Discussion (Misc queries) | |||
| calculate average in percentage coloumn with #DIV/! (ignore error | Excel Discussion (Misc queries) | |||
| Need to sum values of columns 1 - 13 and 4 - 15 | Excel Worksheet Functions | |||
Linear Mode
