How Does This Formula Work??

msimone

New Member
Joined
Sep 30, 2006
Messages
17
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
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
Joined
Sep 30, 2006
Messages
17
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
Joined
Sep 30, 2006
Messages
17
NBVC,

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

Thanks again,

Mark
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top