cell address formula

[John Caines]

Hello All.
I have a workbook, & in Cell M14 I have the following formula to show the lowest £ amount from a range of cells, Range (K24:K523)

{=IF(COUNTA($F$6:$F$10)<5, "", MIN(IF(L24:L523 = "LOSS", K24:K523)))}

It is an array, just to clarify,,,I think,,

All I want to do is put a formula in cell L14 that says "The Cell Number this £ amount was found in from the range (K24:K523)
So,, IE if the amount was £760 for the formula cell M14,,,and it was found in the K range of cells,, say K333,,,,,,
I would like "K333" to be displayed in cell L14


I hope the above makes sense.
I've tried a few things,, just can't get it
Many Thanks for your time,
All the best
John Caines

 

[AlphaFrog]

Try something like this array formula...

=IF(M14="","","K"& 23+MATCH(M14,IF(L24:L523="LOSS",K24:K523),0))

 

[John Caines]

Hello AlphaFrog!!
Great stuff,, just tried it,,, works perfectly,, many thanks for this AlphaFrog...
I can now alter it also for another cell I have for the largest amount,, I'll try this in a minute..

1 more question if I may as it is on the same theme.

In cell M12 I have a formula which is for the "Largest Losing Streak",,, NOT most consecutive losses,, as this could be different,, but largest losing streak..the code is;

=IF(COUNTA($F$6:$F$10)=5,MIN(X24:X523),"")

Say this returns an amount of £2500
What I'm looking for AlphaFrog is to do similar to what you've coded already. In cell L12 I would like it to show the cells this relates to.
Here's the twist,,, the answer could be say L272:L278 (IE 8 cells).

How to code this is a bigger problem I feel ,, way above me.
Any Ideas AlphaFrog??
many thanks for your reply anyway,,
works a treat.
many thanks again
john Caines

 

[AlphaFrog]

You need to better explain what's in X24:X523 and how the min value in the range equates to "Largest Losing Streak".

We can find the cell address in X24:X523 that contains your example value £2500. What criteria determines the address range above, below, or around £2500 that constitutes a "Streak"?

 

[John Caines]

Many thanks for getting back to me AlphaFrog,,,

In Column X the formula (for cell x24 is;

=IF(AND(L24="LOSS",L25<>"LOSS"),SUMIF(L$24:L24,"LOSS",K$24:K24)-SUM(X$23:X23),"")

Cell X25 formula is

=IF(AND(L25="LOSS",L26<>"LOSS"),SUMIF(L$24:L25,"LOSS",K$24:K25)-SUM(X$23:X24),"")

I had a lot of help with these formulas AlphaFrog,, so maybe I'm not the best to explain how they work,, but basically Column X looks for losing streaks,, IE if there are 2 Losses or more in a row,, then if a win comes up,, it recognises this,, and so just stops that lossing streak count and starts again when there are 2 more losses in a row ,,,,until Cell X523

I hope this helps...
I can always PM you the spreadsheet,, it's an xlsm,,

Hope the above helps AlphaFrog.


As a note AphaFrog,,, I don't want to confuse the issue,, but I will mention 2 other formulas for 2 other cells I have,, as this might help in finding a solution (Then again it might not,, but hey,, what the hell,, I'll show you anyway)
For cellL10 which is "Most Consecutive Losses I have a formula;

={IF(COUNTA($F$6:$F$10)=5,MAX(FREQUENCY(IF(L24:L523="LOSS",ROW(L24:L523)),IF(L24:L523<>"LOSS",ROW(L24:L523)))),"")}

To know where these are found I have the code in this cell, Cell M10 which is;

{=IF(N(L10),SUBSTITUTE(CELL("address",INDEX(L24:L523,MATCH(TRUE,COUNTIF(OFFSET(L24:L523,ROW(INDIRECT("1:"&ROWS(L24:L523)-L10+1))-1,0,L10),"LOSS")=L10,0))),"$","")&":"&SUBSTITUTE(CELL("address",INDEX(L24:L523,MATCH(TRUE,COUNTIF(OFFSET(L24:L523,ROW(INDIRECT("1:"&ROWS(L24:L523)-L10+1))-1,0,L10),"LOSS")=L10,0)+L10-1)),"$",""),"")}

I've posted these Alpha Frog as these might help,, as the last above formula returns the correct formatting,,, for example "L94:L99" (If there were 6 consecutive losses)

I hope this all helps AlphaFrog.
these formulas are all way above me.

Many thanks again.
John Caines


Many Thanks
John Caines..

 

[John Caines]

Just to add AlphaFrog I have 1 more hidden column,
which is column W,, Labelled "Winning Streaks £'s (cell range W24:W523)

This has the formula in cell W24

=IF(AND(L24="WIN",L25<>"WIN"),SUMIF(L24:L$24,"WIN",K24:K$24)-SUM(W$23:W23),"")

W25 is

=IF(AND(L25="WIN",L26<>"WIN"),SUMIF(L$24:L25,"WIN",K$24:K25)-SUM(W$23:W24),"")

Just to show you the opposite formulas,, IE Winning Streaks AlphaFrog

Many thanks again for your help

All the best
John Caines