Max and VLookUp together?

[RogerPenna]

I have a Risk Analysis spreadsheet.

User registers a Risk, the number of the analysis, analysis date, risk probability, risk impact, days for next analysis. For the sake of clarity, lets not even consider the risk probability and risk impact.

So you have like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]RISK NAME[/TD]
[TD]# ANALYSIS[/TD]
[TD]DAYS NEXT ANALYSIS[/TD]
[TD]DATE NEXT ANALYSIS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]Low Budget[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]1/3/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]High Taxes[/TD]
[TD]1[/TD]
[TD]60[/TD]
[TD]1/3/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]Project Delays[/TD]
[TD]1[/TD]
[TD]90[/TD]
[TD]1/4/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/18[/TD]
[TD]Low Budget[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]1/6/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/18[/TD]
[TD]High Taxes[/TD]
[TD]2[/TD]
[TD]60[/TD]
[TD]1/6/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current date: 11/07/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ok, notice I registered 3 risks and made an analysis number 1 of them. Two of the risks I decided to make a new analysis in 60 days. [DATE NEXT ANALYSIS] is the sum of [DATE]+[DAYS NEXT ANALYSIS]

So, on march, I re-analysied two of the risks. The 3rd risk, I should analyse only on April.

There are some formatting rules in place which make the DATE NEXT ANALYSIS get red, as the date approaches. HOWEVER, if a new analysis of that risk was made, I need to change the conditional formatting (to blue for example), so users seeing a big list of risks won´t get confused on which risks were already re-analysed and which ones are really pending a new analysis.



OK, enough explanations about the table. Formulas. I decided to have a hidden column that will tell the conditional formatting of [DATE NEXT ANALYSIS] if it should be blue or not.

My problem is with the formula for that hidden column.


It should check [RISK NAME], where any row with a Risk name Equal to the current row, but with a higher #Analysis , will mark as TRUE. Meaning that a new analysis of that risk was done and therefore the Data Warning should be blue, or set to zero, whatever.


Right now, all DATE NEXT ANALYSIS are RED, because current date is july and all rows have past dates.

However, I need the first two rows to be BLUE (because those two risks already had a 2nd analysis, so only NEXT DATE for analysis number 2 should be red.
Third row however must still be red, as the third risk did not get a 2nd analysis yet, so the DATE NEXT ANALYSIS of the first analysis is the one that is delayed.

 

[RogerPenna]

Re: Max and VLookUp together? Or is there another way?

The column to calculate TRUE OR FALSE on line 2 (the first risk) should:

check all instances of [RISK NAME] with "Low Budget". Result are Lines 2 and 5.

Check Analysis Number of Lines 2 and 5. That's 1 and 2.

Get the Max out of 1 and 2. That's 2.

Check if 2 is bigger than that row (2) Analysis Number (1). Yes. So result is TRUE. NEXT DATE ANALYSIS is then conditionally formatted to be blue, based on TRUE result.


Suppose I create a 7th row, again with LOW BUDGET ANALYSIS. It's Analysis Number 3.
Row two check: TRUE (as before)

check all instances of [RISK NAME] with "Low Budget". Result are Lines 2, 5 and 7.

Check Analysis Number of Lines 2, 5 and 7. That's 1, 2 and 3.

Get the Max out of 1, 2 and 3. That's 3.

Check if 3 is bigger than that row (2) Analysis Number (1). Yes. So result is TRUE. NEXT DATE ANALYSIS is then conditionally formatted to be blue, based on TRUE result.

Row five check: TRUE

check all instances of [RISK NAME] with "Low Budget". Result are Lines 2, 5 and 7.

Check Analysis Number of Lines 2, 5 and 7. That's 1, 2 and 3.

Get the Max out of 1, 2 and 3. That's 3.

Check if 3 is bigger than that row (5) Analysis Number (2). Yes. So result is TRUE. NEXT DATE ANALYSIS is then conditionally formatted to be blue, based on TRUE result.

Row seven check: FALSE

check all instances of [RISK NAME] with "Low Budget". Result are Lines 2, 5 and 7.

Check Analysis Number of Lines 2, 5 and 7. That's 1, 2 and 3.

Get the Max out of 1, 2 and 3. That's 3.

Check if 3 is bigger than that row (7) Analysis Number (3). NO, IT'S THE SAME. So result is FALSE. NEXT DATE ANALYSIS is then conditionally formatted to as RED based on date being lower than current date.

 

[RogerPenna]

Re: Max and VLookUp together? Or is there another way?

I guess that shortening the question, it is this:

What formula can i use that will find me the biggest number in [#ANALYSIS] that corresponds to a risk in [RISK NAME] which is the same as the [RISK NAME] in current formula row?

 

[vds1]

Re: Max and VLookUp together? Or is there another way?

If risk name is in column B and # analysis in column C then try the below formula,

=MAX(IF($B$2:$B$6=B2,$C$2:$C$6))

This should be confirmed by ctrl+shift+enter (Not just enter) since its array formula.

 

[Marcelo Branco]

Re: Max and VLookUp together? Or is there another way?

Maybe this

=LOOKUP(2,1/(B$2:B$100=B2),C$2:C$100)>C2
copied down

M.

 

[RogerPenna]

Re: Max and VLookUp together? Or is there another way?

Maybe this

=LOOKUP(2,1/(B$2:B$100=B2),C$2:C$100)>C2
copied down

M.

Obrigado Marcelo.

The array formula by VDS1 worked flawlessly, however, for one reason or the other, Excel crashed while I tried to do some tests.

I don´t really understand your formula.

The first value of Lookup is the value that the formula should look up. Why it's looking "2"? It could be 1, 2, 3, 4....

ps: no need to copy it down. It's a table.

 

[Marcelo Branco]

Re: Max and VLookUp together? Or is there another way?

This part of the formula
LOOKUP(2,1/(B$2:B$100=B2),C$2:C$100)
returns the value in column C$2:C$100 correspondent to the last occurrence of the value in column B (B2 in this case)

To understand this part take a look at (excellent explanation by Aladin Akyurek)
https://www.mrexcel.com/forum/excel...alue-unsorted-data-post492425.html#post492425

M.

 

[Marcelo Branco]

Re: Max and VLookUp together? Or is there another way?

I meant:
This part of the formula
LOOKUP(2,1/(B$2:B$100=B2),C$2:C$100)
returns the value in C$2:C$100 correspondent to the last occurrence of B2 in B$2:B$100

M.

 

[RogerPenna]

Re: Max and VLookUp together? Or is there another way?

This part of the formula
LOOKUP(2,1/(B$2:B$100=B2),C$2:C$100)
returns the value in column C$2:C$100 correspondent to the last occurrence of the value in column B (B2 in this case)

To understand this part take a look at (excellent explanation by Aladin Akyurek)
https://www.mrexcel.com/forum/excel...alue-unsorted-data-post492425.html#post492425

M.

Thanks, but this is waaaaay over my head. It seems to be based on an Excel flaw? Like using a flaw to get something right out of an unintended consequence?

How do I mark this topic as solved? I am using the array solution, that worked flawlessly. I always forget about array solutions...

 

[Marcelo Branco]

Re: Max and VLookUp together? Or is there another way?

How do I mark this topic as solved?

No need, "Thanks" is enough.

You are welcome.

M.