Excel - Explanation of code 1

[Excelerate2004]

Hello,

I used the following code in a project a while back, but now a user is having difficulty with one of the calculations that uses this code.

Would someone be able to help me decipher just what exactly this code is actually doing in pseudo-code? I'm not completely sure since its been a while.

Once I figure out what it does then maybe I can formulate a question as to how to resolve the current problem I'm having with it.

=IF(ISERROR(SUM(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))))/COUNTIF(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))),"<>NA")),"NA",SUM(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))))/COUNTIF(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))),"<>NA"))


Thanks for the help

 

[xlbo]

Without the appropriate data, that's gonna be very hard to break down. Your best bet is to try and disentangle it yourself - try copying sections of it into seperate cells and seeing what individual bits of the formula do.

As far as I can see, the formula is really:
SUM(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))))/COUNTIF(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))

as the rest of it is error checking and duplication in testing for errors.

That formula is essentially

SUM(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))))

and

COUNTIF(OFFSET('Step 2'!C11,,,3,(5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))),"<>NA"))

The offset and MATCH functions are obviously returning a range and the COUNTIF is counting how many entries are <>NA

The offset and match are the same each time so I would suggest that the formula is summing a dynamic range and dividing that by the number of entries that are not NA (which would probably have a value of 0 - so it is just getting an average - your only difficulty should be in understanding how the range is being defined....a quick F1 and search for OFFSET and MATCH should set you on your way there !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

ps - for future ref, you are referring to a formula - code is an entirely different thing

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Excelerate2004]

Your explanation of the formula is pretty much dead on, however can you tell me what the three commas after the cell reference are doing?

EX: 'Step 2'!C11,,,3,

Also what is "(5-MATCH" doing?

Thanks

 

[xlbo]

This is not a helpdesk. As such, you are expected to try some of this yourself. Look in the help files for the OFFSET function as the 'Step 2'!C11,,,3, is part of that

Also look at the MATCH function asd the tru expression is:
5-MATCH('Step 1'!B6,{10,5,2,-0.0001},-1))

hint - the MATCH formula is doing a closest match on 10, 5,2 and -0.0001 and subtracting that result from the number 5

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

You have a matrix of cells in sheet "Step 2.xls" consisting of three rows and up to 4 columns in C11:F13

You have a test value in sheet "Step 1.xls" in B6

The formula is returning the average of specified cells in the matrix based on the test value:
[tt]
Test Value Range to be averaged
---------- --------------------
Minus infinity to -0.0001 C11:C13 (one column)
-0.00000999999 to 2 C1113 (two columns)
2.000000000001 to 5 C11:E13 (three columns)
5.000000000001 to 10 C11:F13 (four columns)
10.00000000001 to infinity "NA"
[/tt]

This formula produces similar results, but may be easier to understand. (N/A values may be different however.)
[tt]
=IF('[Step 1.xls]Sheet1'!$B$6<=-0.0001,AVERAGE('[Step 2.xls]Sheet1'!$C$11:$C$13),IF('[Step 1.xls]Sheet1'!$B$6<=2,AVERAGE('[Step 2.xls]Sheet1'!$C$11:$D$13),IF('[Step 1.xls]Sheet1'!$B$6<=5,AVERAGE('[Step 2.xls]Sheet1'!$C$11:$E$13),IF('[Step 1.xls]Sheet1'!$B$6<=10,AVERAGE('[Step 2.xls]Sheet1'!$C$11:$F$13),"NA"))))
[/tt]
It might be a bit easier to understand what is happening if you allocate some working cells: A secondary test cell to contain the analyzed test result and four cells to contain the possible 4 averages. Then choose which one to take according to the secondary test cell (I used row 8, but you can put them anywhere):
[tt]
B8: =AVERAGE('[Step 2.xls]Sheet1'!$C$11:C$13)
C8: =AVERAGE('[Step 2.xls]Sheet1'!$C$11$13)
D8: =AVERAGE('[Step 2.xls]Sheet1'!$C$11:E$13)
E8: =AVERAGE('[Step 2.xls]Sheet1'!$C$11:F$13)
F8: =5-MATCH('[Step 1.xls]Sheet1'!B6,{10,5,2,-0.0001},-1)
G8: =CHOOSE(IF(ISNA(F8),5,F8),B8,C8,D8,E8,"NA")
[/tt]

 

[Excelerate2004]

After spending quite a bit of time looking at the MATCH and OFFSET Functions and realizing thats getting me no where, I've tried using Functions that I know to achieve my end result.

I'm using the following examples:

COUNTIF(A3:A6,"<>NA")
SUM(A1:A5)
ISERROR(value)
-------------------------------------------------------

I'd like to be able to combine the following 4 statements into one

These are my 3 basic IF statments:

1.
IF('Step 1'!B6<=2,SUM('Step 2'!C1112)/COUNTIF('Step 2'!C1112, "<>NA")

2.
IF('Step 1'!B6<=5,SUM('Step 2'!C11:E12)/COUNTIF('Step 2'!C11:E12, "<>NA")

3.
IF('Step 1'!B6<=10,SUM('Step 2'!C11:G12)/COUNTIF('Step 2'!C11:G12, "<>NA")


Plus I'd like to be able to catch the following error: #DIV/0! as the possibility that there may be an "NA" tag in one of my cells and the error would occur due to the calculations in the IF statements above.

4.ISERROR
ISERROR("NA")


Therefore, I've tried this:

=ISERROR(IF('Step 1'!B6<=2,SUM('Step 2'!C1112)/COUNTIF('Step 2'!C1112,"<>NA"),IF('Step 1'!B6<=5,SUM('Step 2'!C11:E12)/COUNTIF('Step 2'!C11:E12,"<>NA"),IF('Step 1'!B6<=10,SUM('Step 2'!C11:G12)/COUNTIF('Step 2'!C11:G12,"<>NA"),"NA"))))

The value I should get is NA however the result I get is equal to TRUE. I think this is almost correct, what am I missing?

Thanks for any help I can get.

 

[kawnz]

this page might help you utilize the iserror function to show your custom error message

http://spreadsheets.about.com/cs/excelfunction1/a/iserrorfunction.htm

 

[mscallisto]

Another trick you can use is to highlight a portion of the formula you wish to evaluate then press F9 to view the value of the highlighted portion.

Press escape key to return to the formula