Repeating if equation

[dimuthumme]

Hi,

I want to repeat the following equation 24 times with the changing values along the column down. After that I want to repeat the same equation again but again starting from "4" value only for A4, B4,C4,D4,G4,E4,J4,H4,L4,K4,I4 . That means equation starts

=IF(O4>11,A4,IF(O4>10,B4,IF(O4>9,C4,IF(O4>8,D4,IF(O4>7,G4,IF(O4>6,E4,IF(O4>5,J4,IF(O4>4,H4,IF(O4>3,L4,IF(O4>2,K4,IF(O4>1,I4,F4)))))))))))

equation after the 24 times

=IF(O27>11,A27,IF(O27>10,B27,IF(O27>9,C27,IF(O27>8,D27,IF(O27>7,G27,IF(O27>6,E27,IF(O27>5,J27,IF(O27>4,H27,IF(O27>3,L27,IF(O27>2,K27,IF(O27>1,I27,F27)))))))))))

Then again equation should start

=IF(O28>11,A4,IF(O28>10,B4,IF(O28>9,C4,IF(O28>8,D4,IF(O28>7,G4,IF(O28>6,E4,IF(O28>5,J4,IF(O28>4,H4,IF(O28>3,L4,IF(O28>2,K4,IF(O28>1,I4,F4)))))))))))

equation will end

=IF(O51>11,A27,IF(O51>10,B27,IF(O51>9,C27,IF(O51>8,D27,IF(O51>7,G27,IF(O51>6,E27,IF(O51>5,J27,IF(O51>4,H27,IF(O51>3,L27,IF(O51>2,K27,IF(O51>1,I27,F27)))))))))))


again it should start similar way.
If possible please help me.I hv attached the sheet and done the same manually starting from cell 'P28'.
Great many thanks.

[oeldere]

Maybe with an VLOOKUP formula in a table (e.g.)

11 12
1 10 11
2 9 10
3 8 9
4 7 8
5 6 7
6 5 6
7 4 5
8 3 4
9 2 3
10 1 2
11 0 1

[ben_hensel]

1) I would build the repeating ranges with an INDIRECT function; that would let me use some math on the ROW function to repeat every 24 rows etc.

eg,
replace all references to A4 with

Please Login or Register  to view this content.

etc,
Then pull down.

2) The IF construction there is unwieldy; I'd move towards an INDEX(output_range, MATCH(test_value, {100,10,9,8,7,6,5,4,3,2,1}, -1)) sort of construction. Or, more ideally, some kind of table like oeldere is pushing for.

3) Your output in the nested TRUE conditions does not match the axis label in A2:M2 ???

[FDibbins]

Try this formula, copied down...
=OFFSET(A4,0,11-INT(O4))

[dimuthumme]

Hi ben_hensel ,

Your method worked well. Thank you very much.
It is being very kind of you.

Hi oeldere,
I didn't look in to your method. Anyway many thanks for everything.

Hi FDibbins,

I tried it in your way. I think I didn't understand it well. Terrific effort.
You guys are awesome.

[FDibbins]

I put that in P28 (where your formulas start?) and then copied down. I added some dummy values to rows 4 & 5 to test, and it seems to work fine.

However, if Ben's suggestion worked for you, thats great

Thanks for the feedback and kind words

[dimuthumme]

Yes, I did the same, but it didn't work. If possible can you attach the sheet