May 18 2021 11:34 AM
I have a spreadsheet that has a code that calculates based on boxes that are checked. Unfortunately, depending on what version of Excel you have, it doesn't work so I have one coded differently that works for the different version. This causes lot's of issues when sharing with schools across the country because we don't know which version they are using.
My question: Is there a code that I can use to achieve the same calculation that will work with ALL versions of Excel?
Here are the two codes (located in J21 on both):
New version: =J17*IF(D10*G10,0.85,IF(D10*G11,0.8,IF(D11*G10,0.8,IF(D11*G11,0.7,))))
Old Version:
=IFERROR(_xlfn.IFS(AND(D11=TRUE,G11=TRUE),J17*0.85,AND(D11=TRUE,G12=TRUE),J17*0.8,AND(D12=TRUE,G11=TRUE),J17*0.8,AND(D12=TRUE,G12=TRUE),J17*0.7),J17)
I have attached both copies unprotected so you can see what I am trying to calculate. Essentially, it is calculating a tax percentage for taxes based on dependents.
May 18 2021 11:58 AM
The IFS function is only available in Excel 2019 and Excel in Microsoft 365, so it won't work in Excel 2016 and earlier versions.
The "New Version" with 3 times IF will work in all versions of Excel. So as long as some of your users have Excel 2016 or earlier, stick with this formula.
May 18 2021 12:16 PM
May 18 2021 12:18 PM
How open are you to more radical changes in this worksheet?
I ask because there are a number of changes that would (I think) make it more robust, and probably easier for the user.
Here are just some examples of things I'd modify and/or question:
There probably would be other things, but those popped out at me.
With regard to your question, (if you're not open to the questions I've asked) I would think that a simple IFS function would work with both the old and new forms. The one you posted is different from the one in the actual spreadsheet......
=IFERROR(IFS(AND(D11=TRUE,G11=TRUE),J17*0.85,AND(D11=TRUE,G12=TRUE),J17*0.8,AND(D12=TRUE,G11=TRUE),J17*0.8,AND(D12=TRUE,G12=TRUE),J17*0.7),J17)
May 19 2021 05:07 PM
May 19 2021 05:44 PM