Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Nested Funtions
Is it possible to add one more argument to this formula?
=IF(COUNT(OFFSET($B138,-4,0,-6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A") I want to specify that if the result of the average is an error, then also return "N/A" |
#2
|
|||
|
|||
=IF(COUNT(OFFSET($B18,-4,0,-6,1))=6,IF(NOT(ISERROR(AVERAGE(OFFSET(C18,-4,0,-
6,1)))),AVERAGE(OFFSET(C18,-4,0,-6,1)),NA()),NA()) -- HTH Bob Phillips "StephanieH" wrote in message ... Is it possible to add one more argument to this formula? =IF(COUNT(OFFSET($B138,-4,0,-6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A") I want to specify that if the result of the average is an error, then also return "N/A" |
#3
|
|||
|
|||
=IF(iserror(AVERAGE(OFFSET(C138,-4,0,-6,1))),"N/A",COUNT(OFFSET($B138,-4,0,-
6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A") ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "StephanieH" wrote in message ... Is it possible to add one more argument to this formula? =IF(COUNT(OFFSET($B138,-4,0,-6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A") I want to specify that if the result of the average is an error, then also return "N/A" |
#4
|
|||
|
|||
Bob Phillips wrote...
=IF(COUNT(OFFSET($B18,-4,0,-6,1))=6,IF(NOT(ISERROR(AVERAGE(OFFSET(C18,-4,0, -6,1)))),AVERAGE(OFFSET(C18,-4,0,-6,1)),NA()),NA()) Maybe =IF(AND(COUNT(OFFSET($B18,-4,0,-6,1))=6,COUNT(OFFSET(C18,-4,0,-6,1))), AVERAGE(OFFSET(C18,-4,0,-6,1)),"N/A") However, I have a big problem with negative 4th & 5th arguments to OFFSET. Makes workbooks much harder to maintain than necessary. Better to write this as =IF(AND(COUNT(OFFSET($B18,-9,0,6,1))=6,COUNT(OFFSET(C18,-9,0,6,1))), AVERAGE(OFFSET(C18,-9,0,6,1)),"N/A") In general, OFFSET(rng,ro,co,rc,cc) could be rewritten as OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) . |
#5
|
|||
|
|||
I'm not sure I understand the advantage to OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) We add a row above the formulas each month and insert more data. The offset takes care of the adjustments were were making each month because of the inserted row. Since we no longer make adjustments, how would eliminating the negative help me? "Harlan Grove" wrote: Bob Phillips wrote... =IF(COUNT(OFFSET($B18,-4,0,-6,1))=6,IF(NOT(ISERROR(AVERAGE(OFFSET(C18,-4,0, -6,1)))),AVERAGE(OFFSET(C18,-4,0,-6,1)),NA()),NA()) Maybe =IF(AND(COUNT(OFFSET($B18,-4,0,-6,1))=6,COUNT(OFFSET(C18,-4,0,-6,1))), AVERAGE(OFFSET(C18,-4,0,-6,1)),"N/A") However, I have a big problem with negative 4th & 5th arguments to OFFSET. Makes workbooks much harder to maintain than necessary. Better to write this as =IF(AND(COUNT(OFFSET($B18,-9,0,6,1))=6,COUNT(OFFSET(C18,-9,0,6,1))), AVERAGE(OFFSET(C18,-9,0,6,1)),"N/A") In general, OFFSET(rng,ro,co,rc,cc) could be rewritten as OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) . |
#6
|
|||
|
|||
"Harlan Grove" wrote in message ups.com... However, I have a big problem with negative 4th & 5th arguments to OFFSET. Makes workbooks much harder to maintain than necessary. Agreed. That was the hardest bit about checking the answer, making sure I covered the correct range. But it is also true of negative 3rd and 4th. |
#7
|
|||
|
|||
StephanieH wrote...
I'm not sure I understand the advantage to OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) Your original formula used function calls like OFFSET($B18,-4,0,-6,1) The arguments are hardcoded, so there should be no big deal changing them to OFFSET($B18,-9,0,6,1) The advantage of this for future maintainers is that the 1st, 2nd and 3rd arguments alone establish the starting (top-left) cell, and the 4th and 5th arguments only determine the size of the range. Using negative 4th or 5th arguments, all arguments are needed to determine the starting (top-left) cell. The ending (bottom-right) cell of the range produced by both of these function calls is B14. That is easier to figure from your function call. So I guess it's a question of whether one prefers it to be easier to figure out the top-left or bottom-right cell in a dynamic range, and that's subjective. We add a row above the formulas each month and insert more data. The offset takes care of the adjustments were were making each month because of the inserted row. Since we no longer make adjustments, how would eliminating the negative help me? Aside from the 1st, the arguments in your OFFSET calls are constants. If you're inserting rows on or above row 18, only the 1st argument would be adjusted by the row insertion. So if you insert 2 rows above row 18, your function call above would become OFFSET($B20,-4,0,-6,1) which would return B11:B16. On the other hand, my formula would become OFFSET($B20,-9,0,6,1) which would also return B11:B16. It's all a question of whether it should be easier to figure out the top-left or bottom-right cell in the range. For me, it's usually better to be able to figure out the top-left cell, but I'll admit that could be subjective. |
#8
|
|||
|
|||
Oh I see what you're saying. In this case, the starting point is always the
same row, but our range moves up a row for each column to the right. It makes it easy to move through the cells and make sure they're following the logic. But, your point is good to know in cases where it makes more sense to see where the top of the range is instead. I'll keep it in mind. Thanks Harlan. "Harlan Grove" wrote: StephanieH wrote... I'm not sure I understand the advantage to OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) Your original formula used function calls like OFFSET($B18,-4,0,-6,1) The arguments are hardcoded, so there should be no big deal changing them to OFFSET($B18,-9,0,6,1) The advantage of this for future maintainers is that the 1st, 2nd and 3rd arguments alone establish the starting (top-left) cell, and the 4th and 5th arguments only determine the size of the range. Using negative 4th or 5th arguments, all arguments are needed to determine the starting (top-left) cell. The ending (bottom-right) cell of the range produced by both of these function calls is B14. That is easier to figure from your function call. So I guess it's a question of whether one prefers it to be easier to figure out the top-left or bottom-right cell in a dynamic range, and that's subjective. We add a row above the formulas each month and insert more data. The offset takes care of the adjustments were were making each month because of the inserted row. Since we no longer make adjustments, how would eliminating the negative help me? Aside from the 1st, the arguments in your OFFSET calls are constants. If you're inserting rows on or above row 18, only the 1st argument would be adjusted by the row insertion. So if you insert 2 rows above row 18, your function call above would become OFFSET($B20,-4,0,-6,1) which would return B11:B16. On the other hand, my formula would become OFFSET($B20,-9,0,6,1) which would also return B11:B16. It's all a question of whether it should be easier to figure out the top-left or bottom-right cell in the range. For me, it's usually better to be able to figure out the top-left cell, but I'll admit that could be subjective. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF statements | Excel Worksheet Functions | |||
€śUse of more then 7 nested if statement€ť | Excel Worksheet Functions | |||
€śUse of more then 7 nested if statement€ť | Excel Worksheet Functions | |||
7+ nested if statement? | Excel Worksheet Functions | |||
nested ifs | Setting up and Configuration of Excel |