Using MOD and IF to find cell value vs reference and show if divisible but I have an issue

Another approach entirely (assuming your VERSION of O365 is reasonably up to date):

=LET(A,H6:V6,B,TAKE(FILTER(A,A<>""),,-3),IF(AND(INDEX(B,,1)>INDEX(B,,2),INDEX(B,,2)>INDEX(B,,3)),"Hat Trick",""))

Your understanding of my formula is spot on.



Please upload an Excel file witha RANGE of scenarios and expected outcomes. Not just one.

So a hattrick is not consecutive weeks of weight loss? It's simply every third week of weight loss, but only shows as such if this is the third week in succession - is this correct?

Not quite. A hat-trick is purely every 3 weeks of weight loss, be they consecutive or not. The key is that once that hat-trick has been achieved, you start again building up those 3 weeks of weight loss over whatever future period.

That's what I meant.

Try this:

=AND(SUMPRODUCT(($I6:$V6<$H6:$U6)*($H6:$U6<$C6))>0,MOD(SUMPRODUCT(($I6:$V6<$H6:$U6)*($H6:$U6<$C6)),3)=0,LOOKUP(2,1/($H6:$V6<>0),$H6:$V6)<LOOKUP(2,1/($H6:$V6<>0),$G6:$U6))

Can be shortened to:

=LET(a,SUMPRODUCT(($I6:$V6<$H6:$U6)*($H6:$U6<$C6)),AND(a>0,MOD(a,3)=0,LOOKUP(2,1/($H6:$V6<>0),$H6:$V6)<LOOKUP(2,1/($H6:$V6<>0),$G6:$U6)))

Not until you've tested it - I don't want to waste time explaining something that doesn't work! Add more data and see if it continues to give the results you want, then let me know and I'll explian it.

OK, well just in case you have already tested it, here's an explanation:

Please Login or Register  to view this content.

SUMPRODUCT(($I6:$V6<$H6:$U6)*($H6:$U6<$C6)

This compares all cells in the range and checks that (a) any cell is SMALLER than the one before it and (b) any cell is less than the starting weight in C6.

AND(a>0

Checks that the SUMPRODUCT formula returns a value more than 0 ...

MOD(a,3)=0

... and that the MOD of that formula equals 0 (i.e. divisible by 3 exactly for the hattrick) ...

LOOKUP(2,1/($H6:$V6<>0),$H6:$V6)

... and that the very last entry in the range ...

<LOOKUP(2,1/($H6:$V6<>0),$G6:$U6)))

... is less then the penultimate value in the range.

If all THREE criteria are met, the formula returns TRUE.

So, after testing, this seems to do exactly what I'm after. Effectively "resetting the count" after each successful 3rd weight loss. Many thanks. I await your response. (I'll try and work it out anyway :D )

See post #12 above yours. Use EVALUATE FORMULA on the FORMULAS ribbon to step through the formula to see what it's doing.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter and rarely necessary.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @AliGW).

If you do need to quote, limit the quoted section just to the section to which you wish to draw your helper's attention or a direct question to which you wish to respond.

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Your ranges have to be equal in length for it to work. It doesn't matter what is in that cell - it just needs to be the one immediately prior to the range of weight entries. You don't need to worry, as nobody is going to score a hattrick with the entry in H6, so it's not going to throw an error, but if it does at the start, then just wrap the whole thing in an IFERROR function.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue 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.

No worries at all - when you come to apply it, if there are issues, just shout here.

I've come across a little issue when I try and put this into production.

I obviously remove all of the dates and entries, and start to enter a new set of weights for "week 1". It's at that point my Hat-trick gives a circular reference to my score totaling cell. See the example. I cannot for the life of me figure out how to ensure that week 1 works correctly. After week 1, it rights itself again.

Equally, my "weekly result" cell (F15) is playing up as it's the first week too. I tried changing the position of the C15 cell in the formula but I think it needs more nuance than that. I'll take another look tomorrow when it's not 11pm, but I know I'll struggle with the Hat Trick one

Is there any chance you might take a look and see what I mean? For the circular reference, once I move on to later dates, it rights itself for some weeks but not others.

circular.jpg

example_cpb.xlsx

Try either this:

=IF(COUNT($S15:$AG15)<2,FALSE,(IF($C15="","",LET(a,SUMPRODUCT(($T15:$AG15<$S15:$AF15)*($S15:$AF15<$C15)),AND(a>0,MOD(a,3)=0,LOOKUP(2,1/($S15:$AG15<>0),$S15:$AG15)<LOOKUP(2,1/($S15:$AG15<>0),$R15:$AF15))))))

or this:

=IF(COUNT($S15:$AG15)<2,"",(IF($C15="","",LET(a,SUMPRODUCT(($T15:$AG15<$S15:$AF15)*($S15:$AF15<$C15)),AND(a>0,MOD(a,3)=0,LOOKUP(2,1/($S15:$AG15<>0),$S15:$AG15)<LOOKUP(2,1/($S15:$AG15<>0),$R15:$AF15))))))

It works correctly as soon as you have more than one number in the range.

The weekly result is a different issue - please start a new thread for that with a suitable title and workbook attached.

The first weight in the so-called hat-trick is NOT below 129, so it doesn't count (based on what you told me before), so there is no hat-trick. Change V6 to 128, and the hat-trick will be valid.

If your rules have changed, then the formula will need to be changed. At the moment, that first value of the three does not pass muster.

Looking at your CF rules, I think they are inaccurate based on the rules you explained earlier in the thread. Green should only kick in when weights drop below the starting weight, e.g =AND(T6<$C6,T6<S6).

Amber would need to be: =OR(T6=S6,T6>=$C6).

I've updated the workbook so that the CF matches your rules (as I understand them) better.

Hello Ali,

Thanks for your response. As I explained in the earlier thread, a hat-trick is just 3 weeks of weight loss. The start weight is only used to determine whether their week 1/first weigh in is a loss or otherwise. From this point, all weight calculations are based on the preceding week's weight value, and [B]not[B] their starting weight.

So why didn't you say that when I explained this:

This compares all cells in the range and checks that (a) any cell is SMALLER than the one before it and (b) any cell is less than the starting weight in C6.

This means that the formula is incorrect after all. I will have a rethink.

Try this instead:

=IF(COUNT($S6:$AG6)<2,FALSE,(IF($C6="","",LET(a,SUMPRODUCT(($T6:$AG6<$S6:$AF6)*($T6:$AG6<>"")),AND(a>0,MOD(a,3)=0,LOOKUP(2,1/($S6:$AG6<>0),$S6:$AG6)<LOOKUP(2,1/($S6:$AG6<>0),$R6:$AF6))))))

Hi Ali,

I really appreciate your assistance here, but your adversarial response, less so. I simply missed your point about the starting weight. My apologies.

Not adversarial at all - just wondering why you didn't challenge it, as I thought you'd understood the explanation of what I had done.

I clearly misinterpreted the requirement in that one respect.

I am entitled to ask questions when I am giving so freely and generously of my own time!

I hope this has fixed the issue, as I am now going to move on. Please remark the thread as solved, if so. If not, then explain in detail what is not working and in what way.

OK - at this point, I'm going to declare myself out.

You need to supply a sample dataset with 20-30 lines of data that can be used by someone helping you for testing purposes - a set that will cover all eventualities that you can possibly think of. Providing one row of data is not enough, I am afraid, no matter how many times you repeat the rules.

I confess to not feeling secure in my understanding any longer - what you are describing seems to be a long way away from what I would consider a hat-trick.

Hopefully someone else will get a better grasp on it. Good luck.

I understand that your requirement hasn't changed - I never said it had. My understanding of it has changed, though, and I don't feel confident enough to move forward on this.

but wanted to use one line to more accurately detail the areas that are incorrect.

But you see that could go on forever!!! If you are only going to provide data that really will only identify one potential issue at a time, it means that there will need to be many tweaks to the formula over time, and that becomes frustrating for helpers who are trying to fully understand what you need and for you when, necessarily, they have to ask questions. And please be clear on this: helpers WILL expect you to have read everything carefully and picked up on any mistakes in their understanding: that is as much your responsibility as it is theirs. It's really important that you check everything really carefully. I am not blaming you: I misinterpreted the brief, but I was surprised that you had missed something crucial when I had taken the time to explain in quite some detail what my formula was doing. I hope this all makes sense and that you don't take it personally.

To be clear: I thought that my formula (the one given today) fully met the brief - I couldn't find anything in my re-reading of your posts that suggested that it did not. So it's not just us not understanding it, it's your not fully demonstrating it as well. It works both ways.

What you need is exemplar data that is detailed snough to cover all eventualuities that you can think of. Then whoever helps has a much better chance of hitting the jackpot at their first attempt. Please provide that now.

I'm more than happy to take another look.

But. I am:

a) not willing to read through 35 previous posts to understand fully;

b) not prepared to make further guesses when there is ONLY one sample made available..

Please restate your ENTIRE requirement and upload a sample with about 10 rows of data that cover ALL possible scenarios AND which contain expected results in ALL cases.

Please highlight the rows where the formula is NOT producing the result you want.

You did NOT include expected answers... I asked for these in ALL cases.

This is probably incorrect, as I suspect Stefan Atkins should be a "". Please ADD expected answers. My confusion arises over whether the HT result should apply to ONLY the current week...

I put the formula in column M to compare with the expected answers that I had expected to see in column K.

You need to ADD a column to the workbook with your EXPECTED results filled in manually. Please do this NOW.

STILL no expected answers... My last guess without expected answers:

=LET(A,S15:AG15,B,FILTER(A,A<>0),C,--(DROP(B,,1)-DROP(B,,-1)<0),IFERROR(IF(AND(MOD(SUMPRODUCT(--(C=1)),3)=0,SUM(C)>0,SUM(TAKE(B,,-2)*{-1,1})<0),"HT",""),""))

It is not that simple as the expected answers changed based on the date in cell value B2

You give expected answers based on the date value that is currently in B2. It is perfectly simple, as I have been saying since the start of this thread.

Please explain WHY row 32 should be FALSE.

EDIT: Forget that - I've just noticed yo uadded a comment.

Yes, I had just edited my last post - I didn't know I was looking for a comment attached to the cell. I was expecting something in a worksheet cell wo it could be seen without having to hover over a cell!

Oh, for goodness sake! Don't be so silly. Of course I want to help - I wouldn't keep trying otherwise.

Try this in K15 copied down:

Please Login or Register  to view this content.

I'll do my best!

=LET(c,SUM(T15<C15),

Checks if T15 is lower than C15 and returns 1 if it is - this is NEW.

a,SUMPRODUCT((U15:AH15<>"")*(U15:AH15<T15:AG15)),

Counts the number of times there is a weight loss for cells that are not blank.

b,LOOKUP(2,1/((T15:AH15<>0)),T15:AH15)<LOOKUP(2,1/((T15:AH15<>0)),S15:AG15),

Checks that the last value in the range is less than the penultimate value.

IFNA(IF(AND(a>0,b=TRUE,MOD(c+a,3)=0),"HT",""),""))

Checks that (a) there are weight losses in the range, (b) that the final value IS lower than the penultimate value, (c+a) adds c to a (this is NEW and makes sure that the first cell is included in the count if it is a weight loss).

Then the MOD kicks in and checks that the count meets that criterion.

Phew!!!

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue 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.

So did it work?

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue 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.

OK. Second place to Ali... an alternative:

=LET(A,HSTACK(C15,T15:AH15),B,FILTER(A,A<>0),C,--(DROP(B,,1)-DROP(B,,-1)<0),IFERROR(IF(AND(MOD(SUMPRODUCT(--(C=1)),3)=0,SUM(C)>0,SUM(TAKE(B,,-2)*{-1,1})<0),"HT",""),""))

Good show!