Comparing cell values with previous cells based on condition

0
votes

I want to compare the value of a cell with all the cells before it and the range of previous cells depends on a condition.

For example, in the below image, I have two people Adam and James. I want to compare B2 with B1, then B3 with both B1 and B2 and so on. But when it comes to James, I want to compare B9 with B8, B10 with B8 and B9 but not with B1-B7, as they belong to Adam.

enter image description here

I've got the previous cell comparison logic working but not the Name condition (formula in column D). How can I do that?

Expected result:

enter image description here

1
excelexcel-formula
Please create an example of what it should look like with the specified formulas, as it's not quite clear what you mean – GalAbra
@GalAbra please see my edit showing the expected result – Anish

1 Answers

2
votes

You need to us MAXIFS() if you have Office 365 Excel or AGGREGATE() if you have later than 2007 or and Array formula if prior to 2007.

MAXIFS()

=IF(A2=A1,IF(B2<MAXIFS(B$1:B1,A$1:A1,A2),"Greater","Lesser"),0)

AGGREGATE()

=IF(A2=A1,IF(B2<AGGREGATE(14,6,B$1:B1/(A$1:A1=A2),1),"Greater","Lesser"),0)

Array formula

=IF(A2=A1,IF(B2<MAX(IF(A$1:A1=A2,B$1:B1)),"Greater","Lesser"),0)

Being an Array formula is must be confirmed with Ctrl-Shift-Enter.

Edit, for all versions (IMHO BEST OPTION)

Or since your data is sorted you can use this formula:

=IF(A2=A1,IF(B2<MAX(INDEX(B:B,MATCH(A2,A:A,0)):INDEX(B:B,ROW()-1)),"Greater","Lesser"),0)

enter image description here