Checkbox code that works across versions

%3CLINGO-SUB%20id%3D%22lingo-sub-2365748%22%20slang%3D%22en-US%22%3ECheckbox%20code%20that%20works%20across%20versions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365748%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20a%20code%20that%20calculates%20based%20on%20boxes%20that%20are%20checked.%20Unfortunately%2C%20depending%20on%20what%20version%20of%20Excel%20you%20have%2C%20it%20doesn't%20work%20so%20I%20have%20one%20coded%20differently%20that%20works%20for%20the%20different%20version.%20This%20causes%20lot's%20of%20issues%20when%20sharing%20with%20schools%20across%20the%20country%20because%20we%20don't%20know%20which%20version%20they%20are%20using.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMy%20question%3A%3C%2FSTRONG%3E%20Is%20there%20a%20code%20that%20I%20can%20use%20to%20achieve%20the%20same%20calculation%20that%20will%20work%20with%20ALL%20versions%20of%20Excel%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20the%20two%20codes%20(located%20in%20J21%20on%20both)%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ENew%20version%3A%3C%2FSTRONG%3E%20%3DJ17*IF(D10*G10%2C0.85%2CIF(D10*G11%2C0.8%2CIF(D11*G10%2C0.8%2CIF(D11*G11%2C0.7%2C))))%3C%2FP%3E%3CP%3E%3CSTRONG%3EOld%20Version%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3DIFERROR(_xlfn.IFS(AND(D11%3DTRUE%2CG11%3DTRUE)%2CJ17*0.85%2CAND(D11%3DTRUE%2CG12%3DTRUE)%2CJ17*0.8%2CAND(D12%3DTRUE%2CG11%3DTRUE)%2CJ17*0.8%2CAND(D12%3DTRUE%2CG12%3DTRUE)%2CJ17*0.7)%2CJ17)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20both%20copies%20unprotected%20so%20you%20can%20see%20what%20I%20am%20trying%20to%20calculate.%20Essentially%2C%20it%20is%20calculating%20a%20tax%20percentage%20for%20taxes%20based%20on%20dependents.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2365748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365823%22%20slang%3D%22en-US%22%3ERe%3A%20Checkbox%20code%20that%20works%20across%20versions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659838%22%20target%3D%22_blank%22%3E%40britt635%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20IFS%20function%20is%20only%20available%20in%20Excel%202019%20and%20Excel%20in%20Microsoft%20365%2C%20so%20it%20won't%20work%20in%20Excel%202016%20and%20earlier%20versions.%3C%2FP%3E%0A%3CP%3EThe%20%22New%20Version%22%20with%203%20times%20IF%20will%20work%20in%20all%20versions%20of%20Excel.%20So%20as%20long%20as%20some%20of%20your%20users%20have%20Excel%202016%20or%20earlier%2C%20stick%20with%20this%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365844%22%20slang%3D%22en-US%22%3ERe%3A%20Checkbox%20code%20that%20works%20across%20versions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659838%22%20target%3D%22_blank%22%3E%40britt635%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20open%20are%20you%20to%20more%20radical%20changes%20in%20this%20worksheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20ask%20because%20there%20are%20a%20number%20of%20changes%20that%20would%20(I%20think)%20make%20it%20more%20robust%2C%20and%20probably%20easier%20for%20the%20user.%3C%2FP%3E%3CP%3EHere%20are%20just%20some%20examples%20of%20things%20I'd%20modify%20and%2For%20question%3A%3C%2FP%3E%3COL%3E%3CLI%3EThe%20input%20expected%20for%20%22Earnings%22%20is%20a%20bit%20ambiguous.%20Since%20all%20the%20figures%20below%20are%20explicitly%20monthly%2C%20I%20assumed%20you%20wanted%20monthly%20earnings%20as%20well%20when%20I%20went%20to%20try%20it%20out.%20But%20no%2C%20you%20were%20expecting%20annual.%20And%20what%20is%20%22Median%22%20about%3F%3F%26nbsp%3B%20So%20the%20clarity%20of%20the%20form%20right%20off%20the%20bat%20could%20use%20some%20attention.%3C%2FLI%3E%3CLI%3EIn%20the%20very%20formulas%20you're%20asking%20about%2C%20you've%20hard-coded%20the%20several%20tax%20rates%20into%20the%20formulas.%20That's%20generally%20a%20%22bad%20practice%22%20because%20it%20requires%20revising%20the%20formulas%20if%2Fwhen%20the%20tax%20percentages%20get%20changed.%20Far%20better%20is%20to%20have%20a%20table%20(in%20a%20hidden%20sheet%20perhaps)%20that%20holds%20those%20values%2C%20makes%20them%20visible%20to%20you%20if%20not%20to%20users%2C%20and%20where%20the%20values%20can%20be%20changed%20and%20from%20that%20cascade%20to%20any%20formula%20that%20uses%20them.%3C%2FLI%3E%3CLI%3EI'll%20grant%20you%20that%20having%20check%20boxes%20for%20%22Married%22%20or%20%22Single%22%2C%20as%20well%20as%20%22Child%22%20or%20%22No%20Child%22%20looks%20fancy%2C%20it%20forces%20you%20to%20write%20more%20complicated%20formulas%20(and%20the%20posted%20examples%20even%20allowed%20me%20to%20check%20BOTH%20entries%2C%20which%20is%20not%20good%20practice%20either%3B%20perhaps%20that's%20because%20it%20was%20un-protected)....anyway%2C%20given%20that%20one%20is%20either%20Married%20OR%20Single%2C%20a%20simple%20%22Y%2FN%22%20or%20have%20the%20question%20be%20%22Marital%20Status%22%20with%20allowed%20answers%20%22M%22%20and%20%22S%22%20(perhaps%20also%20%22W%22%20for%20widowed%2C%20%22D%22%20for%20divorced...)%20all%20of%20which%20could%20lead%20(via%20a%20table)%20to%20simpler%20formulas%20than%20the%20one%20you%20have.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20probably%20would%20be%20other%20things%2C%20but%20those%20popped%20out%20at%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regard%20to%20your%20question%2C%20(if%20you're%20not%20open%20to%20the%20questions%20I've%20asked)%20I%20would%20think%20that%20a%20simple%20IFS%20function%20would%20work%20with%20both%20the%20old%20and%20new%20forms.%20The%20one%20you%20posted%20is%20different%20from%20the%20one%20in%20the%20actual%20spreadsheet......%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(IFS(AND(D11%3DTRUE%2CG11%3DTRUE)%2CJ17*0.85%2CAND(D11%3DTRUE%2CG12%3DTRUE)%2CJ17*0.8%2CAND(D12%3DTRUE%2CG11%3DTRUE)%2CJ17*0.8%2CAND(D12%3DTRUE%2CG12%3DTRUE)%2CJ17*0.7)%2CJ17)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

5 Replies

@britt635 

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.

Thanks Hans! We use the "New Version" as a default with our program, but quite frequently run into issues where it doesn't work for them in which case we have to end up sending the the "Old Version" for it to work. It just causes a lot of confusion. Do you know why this would be or what we could do to avoid this issue?

@britt635 

 

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:

  1. The input expected for "Earnings" is a bit ambiguous. Since all the figures below are explicitly monthly, I assumed you wanted monthly earnings as well when I went to try it out. But no, you were expecting annual. And what is "Median" about??  So the clarity of the form right off the bat could use some attention.
  2. In the very formulas you're asking about, you've hard-coded the several tax rates into the formulas. That's generally a "bad practice" because it requires revising the formulas if/when the tax percentages get changed. Far better is to have a table (in a hidden sheet perhaps) that holds those values, makes them visible to you if not to users, and where the values can be changed and from that cascade to any formula that uses them.
  3. I'll grant you that having check boxes for "Married" or "Single", as well as "Child" or "No Child" looks fancy, it forces you to write more complicated formulas (and the posted examples even allowed me to check BOTH entries, which is not good practice either; perhaps that's because it was un-protected)....anyway, given that one is either Married OR Single, a simple "Y/N" or have the question be "Marital Status" with allowed answers "M" and "S" (perhaps also "W" for widowed, "D" for divorced...) all of which could lead (via a table) to simpler formulas than the one you have.

 

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)

 

@mathetes,
Thank you for spending so much time looking at my sheet and providing some great feedback! It's kind of hard to comprehend everything that is going on without context, but the spreadsheet works synonymous with the information they collect with a school program, hence the entry and median salaries. They do research on college and careers and ultimately pick one out to take on a test drive for "real life" while making decisions for housing, transportation, groceries, etc.. It's audience is junior and senior high school students so we are trying to make it look as uncomplicated as possible, while still providing all of the information they need to move onto the next part of the program. I am always open to ideas! I like your second idea and will have to look into that when I have more time. I would say I am a proficient beginner, so these forums (and your feedback) have been really key in my learning.

All that being said, the code you provided at the bottom of your comment is exactly what I am looking for right now, however, when I plug it in on my computer with the newer version of Excel, it doesn't work. Any other ideas?
I don't know why that formula wouldn't work on the newer version if it works on the older.

But maybe there's a cell reference or some other easily overlooked nit that throws off the whole thing. What I'd suggest to de-bug it is take each of the sub-conditions (if I can call them that)--each condition that needs to be evaluated--and run them individually in the cells where the whole has to work. My guess is that one or more of them is messing things up.