Cell value dependent on multiple conditions

My range1 is D4:M4 & range2 is D6:M6. Both range1 & range2 consists numerical values
Range3:D10:M10 which consists of “1” or “0”

Output cell: Q6 which should give result as per the below conditions:
1. Formula should check the ‘last’ cell in which “1” appears in D10:M10
2. Now it has to consider range1 & range2 both up to the column in which ‘last’ “1” appears.
3. Then starting from the 1st column of range1 and range2, Q6=D6,IF(D4>E4 AND D6>E6)
4. Else Q6=D6,IF(D4>F4 AND D6>F6),E6,IF(E4>F4 AND E6>F6)
5. Else Q6=D6,IF(D4>G4 AND D6>G6),E6,IF(E4>G4 AND E6>G6),F6,IF(F4>G4 AND F6>G6)
And so on…….

Q6=0, IF all D10:M10=0 OR all cells of range1=0 OR all cells of range2=0

How to accomplish?
Thanks in advance.

Some modification in the formula may be required.
To check its working, I changed 1 value i.e. H10=0 (from H10=1), but the formula gives G6 i.e. 320 (wrong).
The last “1” appears in G10 so the range to be considered is D4:G6…although G4<F4 & also G4<E4 but G6 is not less than F6 (corresponding cell) & G6 is not less than E6 (corresponding cell) respectively for the formula to give G6 as answer.
Here since this condition does not fulfills, so the answer would be “” (null).

Modification in the formula is requested.

No, Q6 still gets L6 (wrong); it should get I6 in this scenario

Last “1” appears in L10. So only up to D2:L6 has to be considered & not complete D2:M6
Now L6 has to be checked in such a way:
1. ‘Last’ value from D6:K6 (before L6) has to be picked for satisfying >L6; plus
2. If I6 is the cell (satisfying condition #1), then it should also satisfy I4>L4
3. If condition #2 is completely met, then Q6=I6 else ‘’ (null)

John, Your formula is very 'near' to the solution. Please see the attached file.

Your formula gives Q6=L6 (274)
It should give Q6=G6 (300)
1. In D6:K6, the nearest value >L6 is K6 (but K4<=L4), so K6 cannot be the answer.
2. In D6:J6, the nearest value >L6 is J6 (but J4<=L4), so J6 cannot be the answer.
3. In D6:I6, the nearest value >L6 is I6 (but I4<=L4), so I6 cannot be the answer.
4. In D6:H6, the nearest value >L6 is H6 (but H4<=L4), so H6 cannot be the answer.
5. In D6:G6, the nearest value >L6 is G6 (Here G4>L4), so G6 is the answer.

If I mark this thread is solved, then can I post this message on any other Excel help website? Then will you treat it as cross posting?

Sir,
I will make it simplest.
1. Answer has to be either of D6:L6
2. For answer=J6, then AND(OR(K10=1,L10=1,M10=1),J6>K6,J4>K4)

I don’t understand T6 or T4, they have been added by you (must have been for some good reasons)

I changed the formula of T10 as =findIt($D$4:$M$4,$D$6:$M$6,$T$4,$T$6)
pasted the new code, but T10 is always giving #VALUE!

It works 99%. When I made K6=297, T10 is giving 297 whereas it should give H6 (343).
I think may be K6 (297) found another 297 (J6), thats all

Thanks a lot John for your extreme support. This is 'what is required by me'. May you have a blessed day & everyday.

John I was trying to understand your code.
The 1st line in the code starts with
Function findIT (......) As String
but the 2nd last line is
finIT = "Null"
I want to ask, 2nd last line should it be
findIT = "Null" OR it is okay.

so the helper cells can be deleted without effecting code's result?

John,
I would be using your code in my Workbook with a Worksheet with tab named XYZ from tomorrow. My Workbook gets real time feeds in a worksheet tab named “7” & the values in it are continuously updated up to a certain time period.

I tried to ‘copy’ your code’s sentences to make it a bigger code so that I can get ‘high’ in T9 & ‘low’ in T10. I could only succeed in getting the ‘high’ in T9 (your code did it, not me) but failed to get ‘low’ in T10. I kept all the columns as it is including T4 & T5 (but deleted U, V, W, X)

I have attached the Workbook. Please tweak the code so that it can also populate ‘low’ in T10.

John,
Your revised code with findhigh & findlow works fine.
1. Can I delete T4 & T6 now?
2. Just like D4:M6; there are 7 more ‘similar’ ranges in this Worksheet which requires ‘similar’ evaluation to throw the required findhigh & findlow. The 7 ranges are:
1. D12:M13 (T12=findhigh & T13=findlow)
2. D15:M16 (T15=findhigh & T16=findlow)
3. D18:M19 (T18=findhigh & T19=findlow)
4. D21:M22 (T21=findhigh & T22=findlow)
5. D24:M25 (T24=findhigh & T25=findlow)
6. D27:M28 (T27=findhigh & T28=findlow)
7. D30:M31 (T30=findhigh & T31=findlow)
3. The ‘controlling’ “1” for all these 7 ranges are the same D10:M10.

Sir, the revised code is required to get the ‘new’ 7 findhigh & findlow in Column T.
This would really help me. I was not expecting vb as a solution but now this is my final requirement.

Originally Posted by JohnTopley

Just change to ranges in

=findhigh($D$4:$M$4,$D$6:$M$6,$D$10:$M$10)

This =findhigh($D$4:$M$4,$D$6:$M$6,$D$10:$M$10) is now already there since your thread #36. Some typo missings?

Okay got it.
Today I tested the code in practical situation.
Code gives a value in findhigh & findlow; against a corresponding last “1” found in D10:M10; even if the corresponding cell in row#x & row#y contains either of “0” (zero) or “” (null). In such a case, findhigh & findlow should be 0 (zero).
Slight revision in the code is requested Sir.

John, now the code works perfectly well. I added
If b(n) = 0 Or b(n) = "" Then GoTo finish

2 times; for the code to take care of cell value being=“0” OR “” for all cells of the 2nd rows.

Many thanks for your time & efforts spent for me.

should this code be pasted in Module?

John,
I tried to tweak your code to meet my requirement, but failed. The ‘tweaked’ code you will find inserted in the file attached.
My requirement:
1. Code should find the cell in D10:M10 with last “1”
2. Corresponding ‘cells’ in D4:M4 & D6:M6 (here K4 & K6)..allow me to call K4 & K6 as ‘base cells’ for further correspondence purpose only
3. In 4th row (corresponding to K4), check the ‘1st cell’ on its left hand side (J4 in this case) findhigh=IF(AND(J4>K4,J6<K6),J4,check ‘2nd cell’ on its left hand side)
findlow=IF(AND(J4>K4,J6<K6),J6,check ‘2nd cell’ on its left hand side)
check ‘2nd cell’ on its left hand side=IF(AND(I4>K4,I6<K6),I4) for findhigh & =IF(AND(I4>K4,I6<K6),I6) for findlow
4. IF no cells in D4:J4 (in this case up to J4 since the corresponding base cell is K4) meets the criteria, then ‘base cells’ becomes J4 & J6 for code’s further checking purpose.
5. Follow criteria #3 i.e. check the ‘1st cell’ on left hand side (I4 in this case)
findhigh=IF(AND(I4>J4,I6<J6),I4,check ‘2nd cell’ on its left hand side)
findlow=IF(AND(I4>J4,I6<J6),I6,check ‘2nd cell’ on its left hand side)
check ‘2nd cell’ on its left hand side=IF(AND(H4>J4,H6<J6),H4) for findhigh & =IF(AND(H4>J4,H6<J6),H6) for findlow
6. Follow criteria #4 i.e. IF no cells in D4:I4 (in this case up to I4 since the corresponding base cell is now J4) meets the criteria, then ‘base cells’ becomes I4 & I6 for code’s further checking purpose….& so on
7. findhigh=’Null’ & findlow=’Null’ IF no criteria is met (example shown in row #36 & 38)

Please help. I tried but was unable to write the correct code (wrong answers in row #12 & 14; row #28 &30)

These conditions will not change any more & is final. Code does 80% of the job...only some additions/modifications to the code will make it complete.

2 input ranges are there in my Worksheet (tab named ‘HL’)
Range 1: D10:M10 which generates either of 1 or 0 or “” (null) with a condition that 1 would be generated in a ‘contiguous’ manner i.e. if 1 is generated in K10 then D10:J10=1

Range 2: 9 sets with each set of 2 rows. The 9 sets are D4:M6; D12:M14; D16:M18; D20:M22; D24:M26; D28:M30; D32:M34; D36:M38 & D40:M42. These 9 sets (with 2 rows each) generates a numerical value >=0 or “” (null)

Answers needed in 9 ranges (Range 3) each having 2 rows. 9 ranges (Range 3) are T4 & T6; T12 & T14; T16 & T18; T20 & T22; T24 & T26; T28 & T30; T32 & T34; T36 & T38; T40 & T42

Criteria’s:
1. Code should find the cell in D10:M10 with last “1” (here in example K10)
2. Then, corresponding ‘cells’ in Range 2:D4:M4 & D6:M6 (here K4 & K6)..allow me to call K4 & K6 as ‘base cells’ for further correspondence purpose only & similarly for other 8 sets of Range 2 i.e. D12:M12 & D14:M14 (here K12 & K14) & so on up to ……. D40:M40 & D42:M42 (here K40 & K42)
3. In 4th row (corresponding to base cell K4), check the ‘1st cell’ on its left hand side of K4 (J4 in this case) findhigh=IF(AND(J4>K4,J6<K6),J4,check ‘next cell’ on its left hand side)
findlow=IF(AND(J4>K4,J6<K6),J6,check ‘next cell’ on its left hand side)
check ‘next cell’ on its left hand side=IF(AND(I4>K4,I6<K6),I4) for findhigh & =IF(AND(I4>K4,I6<K6),I6) for findlow
4. IF no cells in D4:J4 (in this case up to J4 since the corresponding base cell is K4) meets the criteria, then ‘base cells’ becomes J4 & J6 for code’s further checking purpose.
5. Follow criteria #3 i.e. check the ‘1st cell’ on left hand side of J4 (I4 in this case)
findhigh=IF(AND(I4>J4,I6<J6),I4,check ‘next cell’ on its left hand side)
findlow=IF(AND(I4>J4,I6<J6),I6,check ‘next cell’ on its left hand side)
check ‘next cell’ on its left hand side=IF(AND(H4>J4,H6<J6),H4) for findhigh & =IF(AND(H4>J4,H6<J6),H6) for findlow
6. Follow criteria #4 i.e. IF no cells in D4:I4 (in this case up to I4 since the corresponding base cell is now J4) meets the criteria, then ‘base cells’ becomes I4 & I6 for code’s further checking purpose….& so on
7. findhigh =’Null’ & findlow=’Null’ IF no criteria is met (example shown in row #36 & 38)
8. findhigh =’Null’ & findlow=’Null’ IF the ‘current’ base cell/(s)=0 or “” (null)

I tried but was unable to write the correct ‘complete’ code (wrong answers in row #12 & 14; row #28 &30)

Appreciate your patience.

I have cross posted in https://www.mrexcel.com/board/thread...teria.1210808/

John,

This code is working up to 1 in K10.

I punched L10=1 (last “1”). Punched some values in L4 & L6; L12 & L14 & so on till L36 & L38; L40 & L42. Here intentionally I kept HIGHEST value in the 1st row of each ranges i.e. L4 (for range D4:M4) & other L (for other ranges). T4 should have given Null (correct answer); but it gives some other values.

T4 & T6; T12 & T14; T16 & T18; T20 & T22; T24 & T26; T28 & T30; T32 & T34; T40 & T42 gives wrong answers (for the values punched in L, these should have given Null as CORRECT answer.

VERY VERY SURPRISINGLY: T36 & T38 gives CORRECT answers.
Please see the attached file with only L changed.

John,

I did exactly same in your code & also for findlow (I just followed your code’s path & did it…I am no where like you)
I am not at all expert in vba.
Today the Workbook ‘was not hanged’ but there are several other Worksheets in this Workbook (may be approximately 18 ws) which has ‘Worksheet codes’ like Private Sub Worksheet_Calculate() & in 2 other Worksheets the codes are…which starts with sentence ….Private Sub Worksheet_Change(ByVal Target As Range).

No sooner the ‘time’ started & D10 populated “1’ in it in the range D10:M10, ALL CODES IN ALL OTHER WORKSHEETS ‘STOPPED WORKING’.

At that moment, though your code inserted in the ‘Module’ of the Worksheet was very much required….& your code was functioning well………..but there was no other choice left with me other than to delete your code from the Worksheet….& then re-started the Workbook & then ALL OTHER CODES in all other Worksheets started ‘working’.

I have no idea as to why this happened…..
Your code is very much required by me but I am stuck what to do?
Currently, I have deleted your code & kept some copies of the Workbook.

Please do help John.

Certainly & I would report back the outcome

Okay
I have kept sequence as
findhigh="Null"
finish:

&

findlow="Null"
finish:

Hope I did correctly

Okay done exactly same

I pasted your latest code, as it is without any change, in the sample Workbook’s Worksheet (without real time feeds) In this I manually changed D10=1 while kept E10:M10=0, the code WORKED CORRECTLY.

But next step when I changed manually E10=1 also (so that D10:E10=1 while F10:M10=0), the code shows error message box “Compile error: Expected End Function”

Sir,

It WORKS. I had identified & added the “End Function” & pasted YOUR LATEST CODE AS IT IS. In the Module

I am taking the T column (findhigh & findlow) of all ranges to another Worksheet in the same Workbook. But I am unable to format these numerical values….example if T4 (findhigh has generated 494.7, the Worksheet’s cell where I have taken this value (494.7), I am trying to format this cell to 2 decimal places but it does not get formatted to 494.70!!! It remains as 494.7

Rest I will report back in the evening.

John,
I would like to describe today’s scenes when the excel Workbook was opened & real time data started coming in.
In the Worksheet, D10=1 IF(9:15 <=NOW<3:30); similarly E10=1 IF(9:30 <=NOW<3:30) & so on till M10. Till then entire D10:M10=0; it becomes 1 one by one from D10 till M10.

At 9:15, D10=1, the wb was hanged & my mouse cursor (arrow shaped) turned into a spinning blue circle. It happened in all ws (this is my guess because I was not able to move from 1 ws to another). The ws were flickering & the cells along with the values were also flickering though the values were updating & as per the excel functions it was performing.

This ‘scene’ kept on until F10 became equal to 1 i.e. when time>= 10:00
Till then (up to time 10:00, i.e. D10=1 or E10=1) your code, PROBABLY, was not running correctly because when E10=1, I noticed all findhigh & findlow in column T were #VALUE! even when it was ‘required’ to populate some values. Here I would say, at D10=1, I could not check the column T.

After>=10:00 i.e. when F10=1, every things became ‘NORMAL’. Hanging, flickering & #VALUE! in column T had ‘vanished’ & it was ‘NORMAL’ thereafter.

Do we need Application.Calculation property like
Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = True; etc
Comments please.

Sir John,
Nothing has been changed. D10:M10 still has the same criteria as well as other cells. I just divulged ‘how’ D10:M10 becomes 1 from 0. By default they are “0” & time function in other worksheets, to which they are linked, make them “1”; one by one. D10=Time!C32, E10=Time!C56……& so on. This I told you so that if it can help you/us to come out of the situation. Can it be justified as I am changing parameters?

Sir you are master & I can’t compel you for anything.

The problem still persists when D10:E10=1 & as soon as F10=1, all problems vanish.

The problem ‘identified’ is all T columns become #VALUE! even when the conditions are met (when E10=1). Here I would definitely say, that I observed today, that ‘sometimes’ column T populated desired values when E10 was “1” but again it became #VALUE!

My guess: When E10=1, the code finds n=2, but then it also makes i=0 somewhere & there is no a(0) or b(0)…which might be giving #VALUE! Sir this is my guess & I am nowhere in comparison to gurus like you.

Can you please make the code act ‘straight’ & give findhigh & findlow in T when n=2 (like you did when n=1). From n=3, the codes can continue in its form.

Your quote:
If E10 is 1 then D10 must be 1 also YES

If E10 is 1 BUT D10 is 0 IT WILL NEVER HAPPEN
Instead of

Please Login or Register  to view this content.

should it be

Please Login or Register  to view this content.

Just want to understand (since I am currently grippling with the same problem)
your quote: does not cause a problem as it will look (backwards) at D4:D6: when n=2, it loops from n-1 to 1 ie, 1 to 1. This works!!!
If the code's sentence is FALSE

Please Login or Register  to view this content.

what will be the Next i (for n=2)

I am a big ZERO in writting code especially analyzing sentences of the code. It may be then n = Application.CountIf(c.Value, 1) is NOT REQUIRED, Sir

Sir Your code in post#79

Please Login or Register  to view this content.

What I am using (IS THERE ANY DISCREPANCY) if you also check I would be more satisfied

Please Login or Register  to view this content.

Sir,
This is one of my last efforts.
I checked & found:
All a & all b in this Worksheet (tab named ZZZ) i.e. D4:M4 & D6:M6; similarly D12:M12 & D14:M14……& so on till D40:M40 & D42:M42 ‘can get’ null OR zero OR a numerical values (since they are getting values from another ws which can generate null OR zero OR a numerical values)

Can this be the ‘cause’ of #VALUE! & hanging/flickering
Now yours………

Will keep this rule also in mind & I'll never do any private messaging..Sorry for the trouble

Okay after seeing your reply I have 'dared' to re-furbish 'your' code. I don't know whether it is correct or not. If you permit I'll post the re-furbished code for your kind perusal, Sir

It works but when D10=1 and then when E10=1, column T ALSO generates #VALUE! (now for 3-4 seconds) & Then the desired value. When #VALUE! is generated (in either of D10 or E10, although if E10=1 then D10 WILL BE 1), hanging + cursor becoming blue spinning ball.
From F10=1, every thing is NORMAL. This is I am unable to 'catch the reason'.

Problem starts when D10=1 & persists only up to E10=1 (of course D10=1 then)

Sir,
Currently I am using your code & it is working very nicely...Currently H10=1 i.e. n=5 D10:H10=1; rest I10:M10=0. Column T is not generating #VALUE! neither hanging/cursor becoming blue spinning ball is happening.

I think if the code is re written
If n>=3 Do While ...Loop
If n=2, straight checks & straight answers (no loops)
If n=1, straight answers (no loops)
This would solve the problem

Sir,
I am not at all good in writing codes. Even this did not worked!!! Even for n=1 (ONLY D10=1 that too punched manually for trial purpose), it does not give findhigh & findlow in column T.........could not solve.....I tried....need your help.....this code is very important for me to run 'smoothly'.