LinkBack URL
About LinkBacks
Hello all,
Looking for a shorter formula using AND and IF functions
The attached WorkBook shows the issue and example of the very long formula I am currently using.
The Sheet is to be used to record skills results for fighting fires (Drills).
Hello all,
Looking for a shorter formula using AND and IF functions
The attached WorkBook shows the issue and example of the very long formula I am currently using.
The Sheet is to be used to record skills results for fighting fires (Drills).
Why specifically If and AND? Do you know that this is the best way forward? I will have a look, but you really should explain fully within your post, giving the formula you are asking us to shorten, not really wholly on an attachment.
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.
Explain in WORDS how the terms "successful", "closer", etc. relate to the H1/H2/H3 lookup table. This requires a simple INDEX MATCH MATCH, I think.
OK
Here is the Formula I am using
Looking for a shorter version please
'=IF(AND(B6="H1",B13="Closer"),$D$19,IF(AND(B6="H1",B13="Successful"),$D$20,IF(AND(B6="H1",B13="Try Again"),$D$21,IF(AND(B6="H2",B13="Closer"),$E$19,IF(AND(B6="H2",B13="Successful"),$E$20,IF(AND(B6="H2",B13="Try Again"),$E$21,IF(AND(B6="H3",B13="Closer"),$F$19,IF(AND(B6="H3",B13="Successful"),$F$20,IF(AND(B6="H3",B13="Try Again"),$F$21,"")))))))))
Explain in WORDS what the formula is doing, please. I don't have time to back-engineer it to find out - sorry. Also, please answer my question in post #3, which you may not have seen.
Based on what I see I would use
Formula:
Please Login or Register to view this content.
I added the possible outcomes of B13 in C column. If the order is different just change it as needed
The first D-F range are all possible outcomes, the first match finds the row in the D-F range where the chosen comment is, the second match finds the column where the type of fire is.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks