confusing formula

[deuce]

Hi,

I have a list of different names/values in multiples of -n, however I find that the IF formula which I created is applying formulas incorrectly. (if(and(b3<b2, b3<b4), Column A1, 0)

I'll use 3 occurences of unique names as an example: Column A : Mike, Mike, Mike, John, John, John, Jane, Jane, Jane, Andrew, Andrew, Andrew, Peter, Peter, Peter, Jason, Jason, Jason.

In Column B: 1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7,8,8,8,9,9,9,10,10,10.

The trouble arises when I drag the formula down, and I'm exhausted working on this.

 

[Fluff]

Fair enough, in that case you can simply copy/paste data to the board.

 

[deuce]

I will explain again (a bit later) on what I'm trying to do and the expected outcome.

 

[deuce]

Columns are name: department: random figures: dates

Andrew a 0.03249192832 0.3467601491 0.0002274135741 01-Sep-2021
Andrew a 0.03306751334 0.3494607639 0.0002283335617 17-Sep-2021
Andrew a 0.0327588522 0.3463091417 0.0002276098885 08-Sep-2021
Andrew a 0.03247705445 0.3463503126 0.0002272013316 07-Sep-2021
Andrew a 0.03250305252 0.3468009468 0.0002273210836 03-Sep-2021
Andrew a 0.03253626616 0.3472879178 0.0002274267411 14-Sep-2021
John b 0.02610850027 0.1288687388 0.0001940288286 02-Sep-2021
John b 0.02636896251 0.1297090376 0.0001946337642 20-Sep-2021
John b 0.02664612961 0.1292420065 0.0001962865731 15-Sep-2021
John b 0.02614683701 0.1287628542 0.0001940175639 13-Sep-2021
John b 0.02679729591 0.1304852502 0.0001954055916 16-Sep-2021
Peter c 0.06904971916 0.1059884774 0.00002301148261 06-Sep-2021
Mike d 0.02776993802 0.04405377883 0.00002247552559 09-Sep-2021
Mike d 0.02750742123 0.04363732645 0.0000223690397 01-Sep-2021
Mike d 0.02810362675 0.04458313721 0.0000226101574 17-Sep-2021
Mike d 0.02770353481 0.04373229578 0.00002244863788 08-Sep-2021
Jane e 0.04585793453 0.03214182302 0.0005007773655 07-Sep-2021
Jane e 0.0456367531 0.03214182302 0.0005007773655 03-Sep-2021
Jane e 0.04607940527 0.03245358156 0.0005032001414 14-Sep-2021

 

[deuce]

Starting from A2 input if(and(a2=a3,a3=a4, b2=b3,b3=b4, c3<c2, c3<c4), A2, 0)

This formula while it gives somewhat correct results has to be improved as there are more than 100 rows dedicated to row in column A, so a solution such as if(and(A2=(A3:A100 & B2=B3:B100) will be useful, however it does not work, which is why I requested help for the problem here. The other part of the problem lies in the fact that the dates are not in chronological order, hence while the formula does not overlap into the names(by using a2=a3 etc), the numerical results are incorrect due to the fact of the dates not being used by the formula in date chronological order. for more information on this please examine the sequences of dates.

So I hope Post#24 explains the required outcome.

 

[Fluff]

So I hope Post#24 explains the required outcome.

Unfortunately not, posting a pseudo formula that doesn't work, does not explain what you are trying to do.
Please clearly explain in words what you need to do

 

[deuce]

it's not a pseudo formula, and is the one i used but with cell references changed. Can we address this in parts, as that would make steady progress.

 

[deuce]

The first part of the formula is intended to prevent name overlap meaning if(and(A2=A3, A3=A4, A4=A5... it goes all the way to A100. and when new rows are added then I need to spend time checking each cell to ensure there is no misapplication. Is there the way to shorten/improve on it to save time?

 

[Fluff]

I've absolutely no idea, because I don't know what you are trying to do.

 

[deuce]

Fluff, Please abandon this post because inspite of my explanations your repeating yourself and making a mountain out of such a small matter, this makes matters worst and is preventing members and scaring them away from contributing.

 

[Fluff]

inspite of my explanations

? You haven't given any. If you don't answer the questions, you are unlikely to get any help.
I will happily leave this thread, as requested.