Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested Formula help
Hello -
I have a sheet which contains data in two columns, E40:F92. Column F contains Last Years data (so all rows have data) ... Column E is this year's data so rows in the 'future' evaluate to #N/A. I need to develop a 4 week pace, comparing this year to last year. Here's a formula I did for a similar application that worked well, but the future weeks were totally blank: =(SUM(OFFSET($E$40,COUNTA($E$40:$E$92)-1,0,1,1):OFFSET($E$40,COUNTA($E $40:$E$92)-4,0,1,1))/SUM(OFFSET($e$40,COUNTA($e$40:$e $92)-1,-3,1,1):OFFSET($e$40,COUNTA($e$40:$e$92)-4,-3,1,1)))-1 I'll make it more confusing -- the last available week of this year (Col E) should be discarded. For example, the data today looks like this: Non-Zero data exists in E40:E80 (E81:E92=#N/A) E80 should be 'thrown out', with the 4wk pace being compared on: Sum(E76:E79)/Sum(F76:F79) The 'offset' formulas above are used to build the range, with the 'counta' formulas used to calc the number of non-blank cells ... this is where the #N/A cells become the problem. Can anyone please help to modify this formula so it does what I need it to? I also need to do an 8 week pace ... TIA, Ray |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested Formula help
Try replacing COUNTA with COUNT. COUNT ignores everything but numbers.
Biff "Ray" wrote in message ups.com... Hello - I have a sheet which contains data in two columns, E40:F92. Column F contains Last Years data (so all rows have data) ... Column E is this year's data so rows in the 'future' evaluate to #N/A. I need to develop a 4 week pace, comparing this year to last year. Here's a formula I did for a similar application that worked well, but the future weeks were totally blank: =(SUM(OFFSET($E$40,COUNTA($E$40:$E$92)-1,0,1,1):OFFSET($E$40,COUNTA($E $40:$E$92)-4,0,1,1))/SUM(OFFSET($e$40,COUNTA($e$40:$e $92)-1,-3,1,1):OFFSET($e$40,COUNTA($e$40:$e$92)-4,-3,1,1)))-1 I'll make it more confusing -- the last available week of this year (Col E) should be discarded. For example, the data today looks like this: Non-Zero data exists in E40:E80 (E81:E92=#N/A) E80 should be 'thrown out', with the 4wk pace being compared on: Sum(E76:E79)/Sum(F76:F79) The 'offset' formulas above are used to build the range, with the 'counta' formulas used to calc the number of non-blank cells ... this is where the #N/A cells become the problem. Can anyone please help to modify this formula so it does what I need it to? I also need to do an 8 week pace ... TIA, Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested formula | Excel Discussion (Misc queries) | |||
Nested formula | Excel Discussion (Misc queries) | |||
Nested Formula help | Excel Worksheet Functions | |||
Nested Formula | Excel Worksheet Functions | |||
Nested Formula | Excel Worksheet Functions |