Nested IF statements and the INDIRECT function in XL2010

[jordk]

Hi,

I am having a few issues with the excel autofill function. Here is what I want it to look like:

[TABLE="width: 545"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]=IF(Sheet2!A1>=4,100,IF(Sheet2!A1=3,75,IF(Sheet2!A1=2, 50, IF(Sheet2!A1=1,25))))[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]=-A1[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]=IF(Sheet2!A2>=4,100,IF(Sheet2!A2=3,75,IF(Sheet2!A2=2, 50, IF(Sheet2!A2=1,25))))[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]=-A3[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]=IF(Sheet2!A3>=4,100,IF(Sheet2!A3=3,75,IF(Sheet2!A3=2, 50, IF(Sheet2!A3=1,25))))[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]=-A5[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 looks something like:

[TABLE="width: 94"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]A[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Which ultimately returns the following in Sheet1

[TABLE="width: 71"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD="align: right"]-25[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


When I drag the formula's down the page, instead of progressing from Sheet2!A1 to Sheet2!A2 to Sheet2!A3 and from -A1 to -A3 to -A5 etc. as it does in the above iteration, it skips a line and ends up looking like Sheet2!A1 to Sheet2!A3 to Sheet2!A5 and -A2 to -A4 to -A6.

[TABLE="width: 585"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]=IF(Sheet2!A1>=4,100,IF(Sheet2!A1=3,75,IF(Sheet2!A1=2, 50, IF(Sheet2!A1=1,25))))[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]=-A2[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]=IF(Sheet2!A3>=4,100,IF(Sheet2!A3=3,75,IF(Sheet2!A3=2, 50, IF(Sheet2!A3=1,25))))[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]=-A4[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]=IF(Sheet2!A5>=4,100,IF(Sheet2!A5=3,75,IF(Sheet2!A5=2, 50, IF(Sheet2!A5=1,25))))[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]=-A6[/TD]
[/TR]
</tbody>[/TABLE]

I have had a look and I think the INDIRECT function may have something to do with it, but I don't quite understand how to apply it in this scenario.

Any help you can provide would be greatly appreciated.

Thanks




[TABLE="width: 195"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

Welcome to the board. Enter the following into A1, then copy to A3, A5, etc.
=MIN(INDIRECT(ADDRESS(ROUNDUP(ROW()/2,0),1,,,"Sheet2"))*25,100)

 

[jordk]

Hi,

Thanks for the quick response, that formula works really well (I don't really understand it but that can be some research for tomorrow :P).

I didn't specify this in the original request, however I have noticed if there is not a number in Sheet2, the formula yields 0.

Is there a way I can have the formula yield "" i.e. nothing when there isn't a number in Sheet2?

I tried =IF(ISNUMBER(Sheet2!A1),MIN(INDIRECT(ADDRESS(ROUNDUP(ROW()/2,0),1,,,"Sheet2"))*25,100),"") however I ran into the same issues I was originally having (skipping from A1 to A3 to A5).

Thanks again,

 

[njimack]

The easiest method would be to format the cell so that if the formula returns 0, it's invisible. Is this acceptable?

Your custom format would be 0;-0;