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:

	A
A1	=IF(Sheet2!A1>=4,100,IF(Sheet2!A1=3,75,IF(Sheet2!A1=2, 50, IF(Sheet2!A1=1,25))))
A2	=-A1
A3	=IF(Sheet2!A2>=4,100,IF(Sheet2!A2=3,75,IF(Sheet2!A2=2, 50, IF(Sheet2!A2=1,25))))
A4	=-A3
A5	=IF(Sheet2!A3>=4,100,IF(Sheet2!A3=3,75,IF(Sheet2!A3=2, 50, IF(Sheet2!A3=1,25))))
A6	=-A5

<tbody>
</tbody>

Sheet2 looks something like:

	A
A1	4
A2	1
A3	2
A4	5
A5	3
A6	5
A7	1
A8	1
A9	4
A10	5

<tbody>
</tbody>

Which ultimately returns the following in Sheet1

	A
A1	100
A2	-100
A3	25
A4	-25
A5	50
A6	-50
A7	100
A8	-100

<tbody>
</tbody>

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.

	A
A1	=IF(Sheet2!A1>=4,100,IF(Sheet2!A1=3,75,IF(Sheet2!A1=2, 50, IF(Sheet2!A1=1,25))))
A2	=-A2
A3	=IF(Sheet2!A3>=4,100,IF(Sheet2!A3=3,75,IF(Sheet2!A3=2, 50, IF(Sheet2!A3=1,25))))
A4	=-A4
A5	=IF(Sheet2!A5>=4,100,IF(Sheet2!A5=3,75,IF(Sheet2!A5=2, 50, IF(Sheet2!A5=1,25))))
A6	=-A6

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[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;