How Does This Formula Work??

[msimone]

Can anyone break down how this fomula works? It was given to me as a solution and it works great in my hypothetical but if I want to apply it practically I need to know how to modify it, but first I want to understand it! Any help is appreciated:

=INDEX(A1:S25,MIN(IF(MOD(ROW(A1:S25)-ROW(A1),5)=0,IF(A1:S25=U1,ROW(A1:S25)-ROW(A1)+1)))+4,MATCH(U1,INDEX(A1:S25,MIN(IF(MOD(ROW(A1:S25)-ROW(A1),5)=0,IF(A1:S25=U1,ROW(A1:S25)-ROW(A1)+1))),0),0))

 

[Doug.T]

I am confused. I don't know what you are trying to do or how this solution "works great".

row(a1) will return 1, the row number of cell a1

row(a1:s25) will return 1, the row number of the top row in the a1:s25 range

If you had ROWS(a1:s25) it would count the number of rows in the range and return 25.

MOD returns the remainder after a division but there is no division and are only whole numbers in your formula.

 

[NBVC]

It would help if you continue on the original thread where you got that answer from....or at least direct people to it, so they can help you decipher it based on your original requirements.

http://www.mrexcel.com/board2/viewtopic.php?t=235734&highlight=

 

[msimone]

Doug T.,

Thanks for taking the time to respond. The formula I posted was what another message board member had constructed for me when I posted my initial question which was deluded down to the following:

a1: 1 (for October 1st)
a2: -$20
a3: +$70
a4: $50 (Subtotal for Oct 1st)
a5: $1000 (current balance)

d1: 2 (for October 2nd)
d2: $100
d3: -$90
d4: $10 (Subtotal for Oct 2nd)
d5: $1010 (current balance)

This repeats in the classic calendar format: 7 cell boxes across and 4 rows downward. The problem I'm having is that if I use HLOOKUP it only it requires that the value I'm looking for be the TOP row in the formula. If I want to have the formula search not only the top row but the 4 calendar box rows underneath I need to edit my formula to such an extent that it becomes useless. Basically I want to have a summary of my current balance on a seperate worksheet depending on the current day.

The crux of my initial question was that I was wonderin if there was a formula (or combination of formulas) that could look in a particular range for a value (today's date, 2 for Oct 2) and once it finds that value, to look down a specified number of rows and return the amount for that day (current balance). Previously I was useing HLOOKUP but as I'm sure you know, HLOOKUP requires that the value your looking for be in the top row. I want to be able to have a formula that doesn't require this. Does that make sense?

 

[msimone]

NBVC,

You're right, thank you for the advice and apologies...I'm new at this board.

Thanks again,

Mark