3 ms·
I am no excel wizard (have been using Libreoffice for a decade now) but I guess you can do that already (in it's own row) =IF(EXACT(ValueA;ValueB); ValueA; "
by atoav 3y ago
I am no excel wizard (have been using Libreoffice for a decade now) but I guess you can do that already (in it's own row)
=IF(EXACT(ValueA;ValueB); ValueA; "Mismatch!")
So you basically just compare two other rows (which you can hide if you like) and if they are exactly the same you display the value, otherwise you display an error.
- pests 3y agoThen color code the row based on that output
- turtleyacht 3y agoThank-you. Yes, it's already there in Excel. The next step may be Don't Repeat Yourself (DRY), so even N nearly duplicate formulae for N rows is N-1 more times than needed. It's not so bad with one column, but after rearranging a dozen columns and copy-pasting some corrections, the question becomes, "Is everything still working okay, or do I need to skip lunch?" There's ways around, like a `CURRENT_ROW()` function. That makes it generic. Understandably, it can be a hassle to type extra functions all the time. Boilerplate for one-liners isn't fun; the whole point is rapid iteration and prototyping. Just saying, if a model is important enough to keep around and maintain, put in some pragmatic checks--just like your example.
- badwolf 3y agoIf I'm doing a lot of moving around of things, and wanting to keep more complicated formulas working, I'll usually just use named ranges for my own sanity (and as a convenience for whoever may need to take over a workbook after me) - eg, ```=SUMIF(TransactionProductSold,"Product1",TransactionAmount)```