• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Planner calculation

jonastiger

Member
Hello
I need help with some formulas (those below) to calculate MIN/MAX in a range by section.
COL N=IF(G3="";MIN(G4:INDEX(G4:G$9941;MATCH(TRUE;INDEX(ISBLANK(G4:G$9941);0;1);0)));G3)
COL O=IF(H3="";MAX(H4:INDEX(H4:H$9941;MATCH(TRUE;INDEX(ISBLANK(H4:H$9941);0;1);0)));H3)
COL P=IFERROR(IF(A3<A4; MIN(INDIRECT("N" & ROW()+1 & ":N" & MATCH(A3; A4:A$9942;0)));L3);"")
COL Q=IFERROR(IF(A3<A4; MAX(INDIRECT("O" & ROW()+1 & ":O" & MATCH(A3; A4:A$9942;0)));M3);"")

They should calculate MIN/MAX sections in yellow cells. Some are calculated, some not.
PlanPrint.JPG

The file attached shows my issue.
Thank you all in advance
 

Attachments

  • Plan_Test.xlsx
    23.8 KB · Views: 1
Last edited:
I made a good progress:

COL N=IFERROR(IF(ISNA(MATCH($A3;$A4:$A$9942;0));MIN(G4:G$9942);IF($A3<$A4; MIN(INDIRECT("g" & ROW()+1 & ":g" & MATCH($A3;$A4:$A$9942;0)));G3));"")
COL O=IFERROR(IF(ISNA(MATCH($A3;$A4:$A$9942;0));MAX(H4:H$9942);IF($A3<$A4; MAX(INDIRECT("H" & ROW()+1 & ":H" & MATCH($A3;$A4:$A$9942;0)));H3));"")
COL P=IFERROR(IF(ISNA(MATCH($A3;$A4:$A$9942;0));MIN(L4:L$9942);IF($A3<$A4; MIN(INDIRECT("L" & ROW()+1 & ":L" & MATCH($A3;$A4:$A$9942;0)));L3));"")
COL Q=IFERROR(IF(ISNA(MATCH($A3;$A4:$A$9942;0));MAX(M4:M$9942);IF($A3<$A4; MAX(INDIRECT("M" & ROW()+1 & ":M" & MATCH($A3;$A4:$A$9942;0)));M3));"")

But still misses something, because last cells don't calculate.

PlanPrint2.JPG
Someone help me with that?
 
Hi
I run in Excel 2019, 2021 and 365

However, I think I solved the problem (in bold):
COL N=IFERROR(IF($A4=0;$A3;IF(ISNA(MATCH($A3;$A4:$A$9942;0));MIN(G4:G$9942);IF($A3<$A4; MIN(INDIRECT("g" & ROW()+1 & ":g" & MATCH($A3;$A4:$A$9942;0)));G3));"")
COL O=IFERROR(IF($A4=0;$A3;IF(ISNA(MATCH($A3;$A4:$A$9942;0));MAX(H4:H$9942);IF($A3<$A4; MAX(INDIRECT("H" & ROW()+1 & ":H" & MATCH($A3;$A4:$A$9942;0)));H3)));"")
COL P=IFERROR(IF($A4=0;$A3;IF(ISNA(MATCH($A3;$A4:$A$9942;0));MIN(L4:L$9942);IF($A3<$A4; MIN(INDIRECT("L" & ROW()+1 & ":L" & MATCH($A3;$A4:$A$9942;0)));L3)));"")
COL Q=IFERROR(IF($A4=0;$A3;IF(ISNA(MATCH($A3;$A4:$A$9942;0));MAX(M4:M$9942);IF($A3<$A4; MAX(INDIRECT("M" & ROW()+1 & ":M" & MATCH($A3;$A4:$A$9942;0)));M3)));"")

But if there is a better approach, I would like to test.
 
Which is the OLDEST version that any solution must work with? Please attach an updated sample workbook.
 
Which is the OLDEST version that any solution must work with? Please attach an updated sample workbook.
The attached file is for EXCEL2021/365
Now I realize there are still some issues to solve (please see the cells in yellow) and I can't find the error
 

Attachments

  • Plan_Test.xlsx
    22 KB · Views: 2
If any solution must work in 2021, then we can't use 365 functions - is this correct?

What errors? Where have you mocked up your expected results? You have given us formulae, but nowhere have you said what their objective is. There is little point in reverse engineering a formula that does not work, so tell us in words what you are wanting it to do and share expected results for the sample dataset.
 
Hi
I've created the file in EXCEL Office Professional Plus 2021, I think is compatible with 365.
However, I used formulae in this file that is common from 2016 version. But the file is to be used in 365.

What is my expectation:
in cols N, O, P, Q formula should calculate MIN/MAX values from the cells below that match an higher level (col A).Otherwise, cell value is equal to value in cols G, H. L, M respectivally. The problem is in the MIN formula (cols N and P).
For Example:
N8 is level 2 (A8), next level 2 is N19 (A19), so N8 should display the MIN value from range N9:N18 (in this range, all levels are higher than 2)
N9 is level 3 (A9), next level 3 is N13 (A13), so N9 should display the MIN value from range N10:N12(in this range, all levels are higher than 3)
and so on...

This is not happening :(
 
But the file is to be used in 365.

But does it NEED to work in 2021 as well? I am trying to establish whether I can safely use 365 functions for this.

I think is compatible with 365.

No - 2021 does not have a lot of the functions available in 365.
 
OK.

In column R, please manually enter the results that you are expecting for all cells highlighted in yellow. I am not following your explanation, so still have no idea what they should be instead of what is being returned.
 
Hi
Please see the attached file
In col R, I´ve included a range scheme
In col S, there are MIN values (reporting to Col G) that should happen in Col N
 

Attachments

  • Plan_Test.xlsx
    24.5 KB · Views: 0
Well, I think I solve the problem:
I've created a helper column that calculates the row of the same level of the active cell.
At the end of table data, col A, I add the level list (1 to 5) - in the attached file, cells A37:A41, in red
Then I adapt my formula based on cols A and B

The ideal is not need the those red cells. but if there is no other option, so be it...

Please let me know if there is other solution

Thanks
 

Attachments

  • Plan_Test.xlsx
    22.8 KB · Views: 2
Back
Top