Hi
Please refer to the enclosed file which makes use of nested OFFSET function.
In other words, an OFFSET function makes use of output from another OFFSET function.
One layer building upon the other.
The file is a simplified scenario.
In real life, things get a little complicated when working with many parameters.
So we are trying to see if there are a simpler formula to achieve our objective.
Thanks for the advice and feel free if you would like to suggest altogether different formulas.
Please insert row 46
C47 drag donw
=MATCH(D$43,INDEX(D$3:D$42,MATCH(C$43,C$3:C$42,)+SUM(C$46:C46)+ROWS(C$46:C46)):D$42,0)-1
Dear Sir
The formular work perfectly when there is only one number on the other side to compare.
However, as the readings are continuously updated, sooner or later there will be more than one reading.
Sorry I did not make this clear in the beginning, but this is the reason the original formula used OFFSET function to avoid counting from top-down.
I illustrate this in the second scenario in the enclosed worksheet.
If there are any further input, it will be most welcome.
Thanks for help so far!
Please try at F47
=IFERROR(MATCH(G$43,INDEX(G$3:G$42,MATCH(2,INDEX(1/(F$3:F$42=F$43),))+SUM(F$46:F46)+ROWS(F$46:F46)):G$42,0)-1,"")
Offset is a volatile function that recalculates every time anything change, Try to avoid if possible.
Thank you sir!
I need more time to test it on real data but the formula seem to work well.
Sorry sir, I found an issue with the formula upon some testing
I am describting in the enclosed file
If you have any advice, would appreciate it
Originally Posted by Bo_Ry
D52
change
=MATCH(D48,INDEX(D3:D42,MATCH(2,INDEX(1/(C3:C42=C48),))+SUM(C51:C51)+ROWS(C51:C51)):D42,0)-1
To
=MATCH(D$48,INDEX(D$3:D$47,MATCH(2,INDEX(1/(C$3:C$47=C$48),))+SUM(C$51:C51)+ROWS(C$51:C51)):D$47,0)-1
Please don't remove "$" you can't drag down without "$". and adjust last refence to last row of data.
If you want to insert rows from before.
Select row 42 and press Ctrl + , formula will auto change reference.
In E46 then drag down
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Sorry sir, I did not explain myself clearly.
The latest row of data in the example tab "After" being Row 47
New readings are added every week, so in a couple of weeks, new rows will be added below Row 47 (like Row 48, Row 49,Row 50, and so on...)
However, even if we remove the "$" sign, when new roles are added, the formular continues to reference to Row 47 only (rather than the latest set of Row 50, etc.)
I explain in the "3 more rows added' tab in enclosed file.
Please try again
C53
=MATCH(D$49,INDEX(D$3:D$48,MATCH(2,INDEX(1/(C$3:C$48=C$49),))+SUM(C$52:C52)+ROWS(C$52:C52)):D$48,0)-1
Select row 48 and press Ctrl + for insert row
5 more rows added sheet are result when insert 5 rows at row 48
C58 automatic change to
=MATCH(D$54,INDEX(D$3:D$53,MATCH(2,INDEX(1/(C$3:C$53=C$54),))+SUM(C$57:C57)+ROWS(C$57:C57)):D$53,0)-1
Do you want the calculation of occurrences should be below the rows in the same column (C & D). Can it be done some other column say K & L starting from 2nd row.
The formula seems to work.
It is a little difficult for us to apply because it requires us to insert an artifical row (green row 48 in your file) below the lastest row of data.
In reality, we are working with many formulas, that make reference to the latest row of data, so that would require us to change all those to accomodate an artificial row.
However, the formula is brilliant and probably better than nested offset function.
Thank you very much!
You see, in reality we are working with over 1,700 rows and many columns, so we prefer to have the formula returned values below the relevant columns, in order for the file to be managable..
Just dropping a note of thanks.
The formula seem to work wells.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks