Hi,
I am trying to determine which column achieves a value first by counting the time it takes to achieve a non-specific value of 90 or higher.
In addition I am also trying to achieve the same for a value of 5 or lower after achieving the max value.
Please help
In your first yellow example, your answer is 9 but 90 is the 10th value. However, your answer for 5 of 22 is the 22nd value. This is true of all your examples. How are you counting?
Formulas that match your sample results are shown to the right of your samples.
Apologies, that was a me rushing.
you are correct, should have been 10th value.
I will try to add this into my worksheet now
That almost works however, I need it to be able to find a value that is not an exact match. I also need it to stop counting the first value above 90...
);
Still struggling with the above, please can someone help.
Please provide a sample workbook with the formula you have been given in place and problem cells highlighted.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Please see the attached spreadsheet
Last edited by Amatthews99; 06-02-2022 at 04:10 AM.
For the high point:
=MATCH(INDEX(Table1[Column1],MATCH(1,(Table1[Column1]>=90.5)*(Table1[Column1]<>0),)),Table1[Column1],0)
and the low point (I think):
=MATCH(INDEX(Table1[Column1],MATCH(1,(Table1[Column1]<=4.5)*(Table1[Column1]<>0),)),Table1[Column1],0)
That works perfectly for the high point but not the low point
Is there a way to offset it from a constant across the columns?
Let me have another look, but it DOES work for the low point in your lates sample file!!!
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Sheet1
This is why a realistic sample file is SOOOOO important.
Will this work for you?
=MATCH(INDEX(Table1[Column1],MATCH(1,(Table1[Column1]<=4.5)*(Table1[Column1]<>0)*(ROW(Table1[Column1])>Q9),)),Table1[Column1],0)
Are you still using Office 2016, or something newer?
Still using Office 2016 (I think)
I will try this now.
Let me know - I have a feeling it might not.
Unfortunately it still doesn't work.
I've updated the sample worksheet.
I'm not surprised - you have removed the cell it refers to!!!
:D
.. Very sorry about that
Try this:
=MATCH(1,(Table1[Column1]<=4.5)*(Table1[Column1]<>0)*(ROW(Table1[Column1])>MATCH(INDEX(Table1[Column1],MATCH(1,(Table1[Column1]>=90.5)*(Table1[Column1]<>0),)),Table1[Column1],0)),)
Which reference figure have you used for "M27"?
Forget that - se my last post.
It works correctly for my sample worksheet but not for my full workbook.
Have I made an error somewhere?
=MATCH(1,(Table245[Probe 3]<=4.5)*(Table245[Probe 3]<>0)*(ROW(Table245[Probe 3])>MATCH(INDEX(Table245[Probe 3],MATCH(1,(Table245[Probe 3]>=90.5)*(Table245[Probe 3]<>0),)),Table245[Probe 3],0)),)
Try setting the formula in your real data as an array formula (CTRL-SHIFT-Enter).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
No change
You'll need to attach a workbook showing it not working! It is clearly working in your last sample workbook. As Glenn said, you may need to enter it as an array formula.
EDIT: Either you've made a mistake or there's something else you haven't told us. PLEASE provide realistic sample data.
Two Minutes I will have to decrease the file size
OK - don't give us TOO MUCH data, but what you do give us needs to show the problem.
An alternative for high:
=MATCH(1,INDEX(--(Table1[Column1]>=90.5),0),0)
and for low:
=AGGREGATE(14,6,ROW(Table1[Column1])/(Table1[Column1]>4.5),1)+1-ROW(G$7)+1
see file.
Please find attached
No, sorry - way too big! And I won't open .zip files - sorry.
Try what Glenn suggested.
I'll downsize it more, sorry about that
Try Glenn's suggestion first!
Ditto. I am on a metered connection in France and the data charges here are truly awful.
Unfortunately I had no success with making it an array.
I've removed all none essential parts now
Try this:
=MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*(ROW(Table245[Probe 1])>MATCH(INDEX(Table245[Probe 1],MATCH(1,(Table245[Probe 1]>=90.5)*(Table245[Probe 1]<>0),)),Table245[Probe 1],0)),)
Same formulae, new file (with 1,000,000 blank rows removed).
Actually, this may work better:
=MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*(ROW(Table245[Probe 1])>MATCH(INDEX(Table245[Probe 1],MATCH(1,(Table245[Probe 1]>=90.5)*(Table245[Probe 1]<>0),)),Table245[Probe 1],0)),)-1
I don't understand why, but it still won't work
Is it possibly because I have blank cells?
I have rearranged your results layout slightly, to facilitate copying data across. V messy with your current layout. I also changed the first formula, which seemed to be giving the wrong answer.
Please indicate WHO you are replying to!!Originally Posted by Amatthews99
I don't understand why, but it still won't work
Never used that function before..
Will move it across and see if it works.
Thank you
It DOES work, if you are referring to me. I have shown it working in the workbook you provided with blanks in the range.I don't understand why, but it still won't work
I've worked with three versions of your data so far and got it to work - the issue is NOT with the formula. Either YOU are making a mistake OR the data in the sample workbook does not correctly reflect your real data, and I cannot mitigate for what I do not know.
The key Q RIGHT NOW is... are the 3 formulae giving the expected answers on your sample sheet??
If so... then it means that if they are NOT working on your real sheet... it's probably something YOU have done wrong, not an intrinsic problem with the formula.
Edit -
Glen-
Just realised what you meant.
Yes, those are the expected values for the sample
Last edited by Amatthews99; 06-02-2022 at 06:37 AM.
OK - if it doesn't work, then please tell us in what way they are not working and provide a sheet showing the problem. So far, what I have provided has matched the results you said were required in the sample workbooks.
Also, as Glenn says, indicate which one of us you are replying to, please, unless you would prefer for me to stand down.
Last edited by AliGW; 06-02-2022 at 06:39 AM.
Glen-
If the data changes the third formula does not work..
Once again, what does "does not work" mean? You have not shown the results you are expecting in the workbook.
This is getting mildly frustrating now.
Try this:
=AGGREGATE(14,6,ROW(Table245[Probe 1])/(Table245[Probe 1]>=4.5),1)+1-ROW(C$7)
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Hot & Cold Spot (90.5蚓 for 10)
Ali -
Apologies, I mean it returns the wrong value.
On the data sheet for collumn C, formula 3 returns 19 when the expected answer is 15. (the first value below 4.5 after the highest value)
To give more context, this data is representative of cooking & cooling. the aim of this is to determine which column reaches less than 4.5 first.
But my formula does this ...
=MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*(ROW(Table245[Probe 1])>MATCH(INDEX(Table245[Probe 1],MATCH(1,(Table245[Probe 1]>=90.5)*(Table245[Probe 1]<>0),)),Table245[Probe 1],0)),)
Ali -
Using =AGGREGATE(14,6,ROW(Table245[Probe 1])/(Table245[Probe 1]>=4.5),1)+1-ROW(C$7)
Returns the same values as yours however the expected values as per my last message. (indicated and highlighted in the sample sheet)
Ali-
Will try this now
=MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*(ROW(Table245[Probe 1])>MATCH(INDEX(Table245[Probe 1],MATCH(1,(Table245[Probe 1]>=90.5)*(Table245[Probe 1]<>0),)),Table245[Probe 1],0)),)
Try this if that second row is going to be there:
=MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*(ROW(Table245[Probe 1])>C4),)
OK. I see the problem. In your real data there's more "wobble" than in your sample.
Ali -
Using: =MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*(ROW(Table245[Probe 1])>C5),)
C:5 is formula 2 =MATCH(1,INDEX(--(Table245[Probe 1]>=90.5),0),0)
It works on my sample sheet correctly.
However, on my workbook it gives the value "1"
This value increases when the value in the first cell is increased above 4.5.
E.G
C16 - 3
C17 -4
C18-90
C19-4
Formula returns - 1
C16 - 5
C17 - 4
C18 - 90
C19 - 4
Formula returns 2
Glenn -
Yes, the real data is random and varies in data size.
The upper and lower limits are the boundaries we use to calculate the time per phase (cooking / cooling)
Cooking (above 90.5)
Chilled (below 4.5)
"Wobble" is highly likely
OK - I'm very close to walking away from this now.
I have asked at least THREE times for you to provide realistic data.
You are drip feeding facts about the dataset, which means that your helpers have to keep rethinking their approach. Please bear in mind that this can become very frustrating, and increases their investment of time considerably, which they are already giving for free.
Is there anything else about the real data that we don't know???
I'm not intentionally drip feeding you information.
Thank you for all the time you have spent on this so far.
I am confused as to why this is not working in my workbook compared to the sample file.
The only difference is file size...
Clearly not, otherwise it would work. Each time we've had a sample workbook, there has been a slight change in the nature of the data: that's what I mean by drip-feeding.The only difference is file size...
Sadly I am out of time for today. Sorry.
Thank you for your help
I've had another look at this.
For second, try:
=MATCH(1,(Table245[Probe 1]>=90.5)*(Table245[Probe 1]<>0),0)
and for third try:
=MATCH(1,(Table245[Probe 1]<=4.5)*(Table245[Probe 1]<>0)*((ROW(Table245[Probe 1])-ROW(Table245[[#Headers],[Probe 1]]))>IFNA(C4,MATCH(MAX(Table245[Probe 1]),Table245[Probe 1]))),)
Hi Ali,
Thank you for all your help.
I was extremely confused as to why my worksheet would not work correctly so I have migrated the data to a new workbook and the below formula worked.
=AGGREGATE(14,6,ROW(Table1[Probe 1])/(Table1[Probe 1]>$E$6),1)-ROW(C25)+2
Once again, thanks for your help
Edit - I only recently solved this and therefore recently marked the thread solved.
OK - I've just wasted another hour on it, then. Grrr!!!
So it was a glitch all along, then? No wonder we couldn't get to the bottom of it. Thanks for letting us know. Lots of blood, sweat and tears on this one!
If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Wasn't aware but I have now done so.
Once again, thank you for your help
No worries.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks