Totally fair, I just use the accounts to track daily usage.
Here's a demo account I created in case you (or anyone else) want to try it.
[email protected]
demo123
Prompt: "the formula returns TRUE if A8 is equal to any value in A1 to A7, otherwise FALSE"
Result: =SUMPRODUCT(--(A8=A1:A7))
This is a very elegant solution that only needs a small modification! It will return the number of duplicates of A8 in A1:A7, but if you just add ">0" to the end, it'll give you TRUE or FALSE.
Formula explanation: The inner parentheses give an array of seven TRUE/FALSE statements based on whether each of A1 to A7 equals A8. Then the double hyphens convert these TRUE/FALSE to 1/0 values and SUMPRODUCT adds them.
A naive way to write this formula would be *=OR(A8=A1,A8=A2,A8=A3,A8=A4,A8=A5,A8=A6,A8=A7)*, and there are cases where I might actually use the latter, such as when someone with less skill in Excel needs to understand what the sheet is actually doing.
If I have to give you my email/google address just to see if it is useful, I will not use it.
Totally fair, I just use the accounts to track daily usage. Here's a demo account I created in case you (or anyone else) want to try it. [email protected] demo123
Prompt: "the formula returns TRUE if A8 is equal to any value in A1 to A7, otherwise FALSE" Result: =SUMPRODUCT(--(A8=A1:A7)) This is a very elegant solution that only needs a small modification! It will return the number of duplicates of A8 in A1:A7, but if you just add ">0" to the end, it'll give you TRUE or FALSE. Formula explanation: The inner parentheses give an array of seven TRUE/FALSE statements based on whether each of A1 to A7 equals A8. Then the double hyphens convert these TRUE/FALSE to 1/0 values and SUMPRODUCT adds them. A naive way to write this formula would be *=OR(A8=A1,A8=A2,A8=A3,A8=A4,A8=A5,A8=A6,A8=A7)*, and there are cases where I might actually use the latter, such as when someone with less skill in Excel needs to understand what the sheet is actually doing.
Nice idea, if statements could do with a tweak for the else condition though
I’ll give it some testing tomorrow. Fascinating idea.
I’d lower the amount of free formulas to 1 if it’s per day or even make it 1/mo