Two Ridiculously Long Formulae - Can these be made less complicated ?
I have two very long formulas.
The first is in Cell N2 of my spreadsheet
PHP Code:
=IF(A2="Six Day",(D2-TODAY())*(B8/365),IF(A2="Six Day 65 +",(D2-TODAY())*(B9/365),IF(A2="Six Day 70 +",(D2-TODAY())*(B10/365),IF(A2="Five Day",(D2-TODAY())*(B11/365),IF(A2="Three Day",(D2-TODAY())*(B12/365),IF(A2="Six Day 18 - 21",(D2-TODAY())*(B21/365),IF(A2="Six Day 22 - 24",(D2-TODAY())*(B22/365),IF(A2="Six Day 25 - 27",(D2-TODAY())*(B21/365),IF(A2="Six Day 28 - 30",(D2-TODAY())*(B20/365),IF(A2="Lady Six Day",(D2-TODAY())*(B15/365),IF(A2="Lady Six Day 65 - 69",(D2-TODAY())*(B13/365),IF(A2="Lady Six Day 70 +",(D2-TODAY())*(B14/365),IF(A2="Social",(D2-TODAY())*(B24/365),)))))))))))))
The second in Cell S2 is:
PHP Code:
=(IF(A2="Men Six Day",B8/365,IF(A2="Men Six Day 65 - 69",B9/365,IF(A2="Men Six Day 70 +",B10/365,IF(A2="Five Day",B11/365,IF(A2="Three Day",B12/365,IF(A2="Six Day 28 - 30",B20/365,IF(A2="Six Day 25 - 27",B21/365,IF(A2="Six Day 22 - 24",B22/365,IF(A2="Six Day 18 - 21",B23/365,IF(A2="Social",B24/365,)))))))))))+IF(F2="YES",B25/365)+IF(G2="YES",B26/365)
Both of these work fine, but are long andpotentially open to errors.
I have restructured this spreadsheet already to match my Excel knowledge ability, but welcome any advice on making it simpler. If it possible, could any explanation be provided on how the revised formula would work.
Any other observations would be welcome. I've only just figured out Index/Match which I have used in C2
Bookmarks