Nested IFs

**Omegaman2** · 02-19-2012, 06:08 AM

I have been working on this for days trying workarounds, I really wanted deeper nesting, but I can live with 8 if it would only work.

here are the two examples, the first one works, the second returns and error - the cell says "#VALUE!"

=IF(J8="tsp. (US)",I8/0.20288,IF(J8="cu. ft.",I8*28316.8466,IF(J8="cu. In.",I8*16.34,IF(J8="cups (US)",I8/0.00422,IF(J8="fl. oz. (US)",I8*29.57352,"N/A")))))

=IF(J8="tsp. (US)",I8/0.20288,IF(J8="Tbs. (US)",I8/0.06762,IF(J8="fl. oz. (US liquid)",I8/0.03381,IF(J8="cups (US)",I8/0.00422,IF(J8="pints (US liquid)",I8/0.00211,IF(J8="quarts (US liquid)",I8/0.00105,IF(J8="litres",I8/0.001,IF(J8="gals. (US liquid)",I8/0.00026,"N/A"))))))))

I have looked over this again and again, but I am not seeing any difference in syntax, do you? If not a syntax error, is there something else I am missing or not understanding about this?

Thanks for all your help and suggestions, to those who take it on!

**excelxlexcel** · 02-19-2012, 06:33 AM

Thats because excel has a limitation on multiple nested ifs, limitation being 7.

If you need to have more that 7 nested ifs try breaking them up into multiple cells, else alternative is to use VBA code.

**royUK** · 02-19-2012, 06:35 AM

Excel 2010 has more than 7 nested functions allowed.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

**Andrew-R** · 02-19-2012, 06:45 AM

The 2nd IF statement works fine for me in Excel 2007.

This function, with no nesting, also works:

=I8/CHOOSE(MATCH(J8,{"tsp. (US)","Tbs. (US)","fl. oz. (US liquid)","pints (US liquid)","quarts (US liquid)","litres","gals. (US liquid)"},0),0.20288,0.06762,0.03381,0.00422,0.00211,0.00105,0.001,0.00026)

I'd like to wrap that lot inside an IFERROR statement, to allow for the return of "NA", but I'm not sure the OP has Excel 2007 or later.

**royUK** · 02-19-2012, 09:37 AM

OP states that he has Excel 2010, but you could use ISNA or ISERROR

**abousetta** · 02-19-2012, 10:36 AM

Here's the way I would run the formula (first check to make sure that metric checked is correct then find the conversion factor within the array). Another simpler option would be to create a lookup table and use VLOOKUP.

Please Login or Register  to view this content.

abousetta

**martindwilson** · 02-19-2012, 10:56 AM

i think you are using totally the wrong approach,id set up a lookup table for everything do the calculation in the table and simply retrieve the result with a vlookup

**abousetta** · 02-19-2012, 10:59 AM

I wouldn't say the approach is totally wrong... just that there are better ways to do it like you suggested. I agree that for long-term maintenance and adaptation, a table and vlookup is a better approach.

**martindwilson** · 02-19-2012, 11:11 AM

i stick by my guns nested if's like that is the wrong approach!!! vlookup may not be either but its certainly better.

**abousetta** · 02-19-2012, 11:13 AM

I guess you mean the original approach the OP suggested because in my example, I am not using nested IF statements. There is only one IF statement and a Lookup. Even so, I still agree that a table is a better practice. No disagreement there.

**martindwilson** · 02-19-2012, 11:52 AM

yes i was referring to op,i wouldn't dare say any solution offered was wrong! (unless it was completely wrong)
perhaps i should put a disclaimer. "unless otherwise stated all comments are directed at OP" yep i will done!

**abousetta** · 02-19-2012, 11:55 AM

Don't worry... Most of my solutions are completely wrong

**Omegaman2** · 02-19-2012, 05:46 PM

Hi All,
First off, let me thank all of you who responded to my dilemna, I really appreciate your efforts and replies. I have not had the chance to try these soltions yet, but zai will. I will comment that there are reasons that I i went with tht particular approach that I did - mainly becuase I have used IF before, and have no idea about lookup tables. I also tried IF(OR, but was not able to get that to work for me either, the way I originally wanted. I only use spreadsheets occasionally, so have never really learned how to use them. Almost everytime to do use them, I learn a thing or two I did not know before. I probably should have come here before my problems, I could have started with the best solution and avoided a lot of failure.

I found it interesting that one reply said it worked for them as is, if I understood the post correctly. For now, I will look over the replies, and try to understand them. I have 152 more cells in this spreadsheet to do with variations of the same bit of code, so maybe I should upload a file, as one poster suggested, then maybe it will be obvious to some of you that there is a hugely better approach.

There is some propriatory information in the file as is, I will need to strip it of those functions before I can do so. Once you see where this section of the sheet is going, maybe I can get advice that suits my purposes the best, so maybe hold off an any further replies until then. I will also insert dome commentary in the file explaining my ideal goal - the way it is now, is a compromise because I could not figure out a way to pull it off - just my ignorance I am sure.

Again, thanks so much, you people are great!

**Andrew-R** · 02-19-2012, 06:23 PM

Originally Posted by Omegaman2

I found it interesting that one reply said it worked for them as is, if I understood the post correctly.

That was me - I pasted your formula into a blank sheet, put some appropriate values into the referenced cells and it worked fine.

Could you confirm which version of Excel you have? Your formula should work in versions from Excel 2007 onwards.

**Omegaman2** · 02-19-2012, 08:15 PM

Okay,

I have stripped the propriatary info out of this, and added what I hope are sufficient notes to communicate what I am ultimately hopin to accomplish. If all goes as planned, this post should have an attachment of my excel 2010 file. Thank you!

**Omegaman2** · 02-19-2012, 08:18 PM

Originally Posted by Andrew-R

That was me - I pasted your formula into a blank sheet, put some appropriate values into the referenced cells and it worked fine.

Could you confirm which version of Excel you have? Your formula should work in versions from Excel 2007 onwards.

Yes Andrew, thanks for that, it is 2010. I find your post to be most intriguing, that you find it works, while I do not. There is no typo, it was cut and pasted straight from the spreadsheet to here.

**vlady** · 02-19-2012, 08:28 PM

hello

is the convert function not applicable?

=CONVERT(A1,"oz","tsp")
...

**Omegaman2** · 02-19-2012, 08:42 PM

sure converter is applicable, it is more an issue of getting the matrix to work with all possible combinations of units. I maybe should have mentioned also, that ultimately, this will not be a in a spreadsheet form, but in a javasript application, and it is just easier for me, to do thinkg that I know will work in that language. , I am not all that sure I can make convert work in that environment, since it is not mere logic and operations, but is working from an internal database built into excel. I prefer to just put conversion factors in myself, so I can see everything it is doing. Thanks you for the suggestion. I am aware of the convert feature, but have not used it yet. I will likely use it in the near future though. Once I get the spreadsheet to do everything correctly, then I can convert it to javascript.

I just got a formula from a friend I asked to help me with this last week, She proposed

=IF(J8="tsp. (US)",I8/0.20288,IF(J8="cu. ft.",I8/0.0000353146667,IF(J8="Tbs. (US)",I8/0.06762,IF(J8="fl. oz. (US liquid)",I8/0.03381,IF(J8="cups (US)",I8/0.00422,IF(J8="pints (US liquid)",I8/0.00211,IF(J8="quarts (US liquid)",I8/0.00105,IF(J8="litres",I8/0.001,IF(J8="gals. (US liquid)",I8/0.00026,"N/A")))))))))

and it seems to work fine for me, Now I just need to do some changes to the J8 and I8 to J9 and I9 across the cc row, and change the conversion factors for the other rows to work with there values.

I am going to mark this thread as solved (if I can figure out how) and will be starring the contributions as I evaluate them. However, if someone, wants to, for the sake of the challenge, figure out how to do it with dropdowns instead of a table, I will still use that in the final version, and probably in some future projects as well, but for now, my friends solution is working for me, so I am going to move forward, finishing the spreadsheet and converting it to javascript.
If you read this, and this thread is not marked as solved, feel free to let me know how that is done.

Nested IFs

LinkBack

Thread Tools

Rate This Thread

Display

Nested IFs

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Re: Nested IFs not working

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions