# How Does This Formula Work??

#### msimone

##### New Member
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))

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Doug.T

##### Board Regular
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.

#### msimone

##### New Member
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

##### New Member
NBVC,

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

Thanks again,

Mark

Replies
5
Views
351
Replies
9
Views
419
Replies
8
Views
2K
Replies
7
Views
887
Replies
2
Views
177

1,171,827
Messages
5,877,778
Members
433,287
Latest member
amna_shahbaz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back