Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-28-2011, 08:40 PM
JimS378 JimS378 is offline Nested IFs Windows 7 64bit Nested IFs Office 2003
Novice
Nested IFs
 
Join Date: Apr 2011
Posts: 5
JimS378 is on a distinguished road
Default Nested IFs

The help file says (I believe) that IFs can be nested 64 deep.
That is: =IF(G5>0,average(G5:G9),IF(G6>0,average(G6:G10),.. ... etc.



I currently have such a formula/function in my spreadsheet which contains 7 IFs and it works OK. But if I try to add an additional "IF", I get an error.

Any ideas? I can send/email an example sheet if need be.

Thanks for any help,

Jim
Reply With Quote
  #2  
Old 04-28-2011, 10:12 PM
macropod's Avatar
macropod macropod is offline Nested IFs Windows 7 32bit Nested IFs Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Jim,

Excel 2003 supports a maximum of 8 nested IFs.

Perhaps you could tell us exactly what you're trying to achieve.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-29-2011, 07:12 AM
JimS378 JimS378 is offline Nested IFs Windows 7 64bit Nested IFs Office 2003
Novice
Nested IFs
 
Join Date: Apr 2011
Posts: 5
JimS378 is on a distinguished road
Default

I have a list of golf rounds data, with handicap differencials in column G. I need, based on the number of rounds in the list, to average a different number of differencials based on how many rounds have been played. I as going to use nested if's - for example:
=IF(G05>0,AVERAGE(G05:G14),IF(G06>0,AVERAGE(G06:G1 4), etc...
This would require about 20 nested IFs.
Guess I misinterpreted what I read about 64 IFs allowed.

Currently, the data is residing in Row 05 to row 24, with the available data ending at row 24 (variable nr of blank lines beginning in row 05). Based on the nr of available lines of data, I need to average a variable nr of cells in column G.

I think the last ELSE argument can point to a continuation cell (to chain together the nested IFs) but not sure how to have the result always end up in the right cell without absolute referencing.


Here is the HELP text that I was referring to. It made me think more than 8 IFs could be nested...

Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.)

Any advise appreciated, thanks in advance.

Jim S
Reply With Quote
  #4  
Old 04-29-2011, 03:56 PM
macropod's Avatar
macropod macropod is offline Nested IFs Windows 7 32bit Nested IFs Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Jim,

Your profile says you're using Excel 2003, which is what my response applied to. The help you're referring to comes from a later version. And, even if you're working with Excel 2007 or later, if the workbook is in the older 97-2003 format, that still won't support more than 8 nested IFs. In any event, there are better ways than using a plethora of nested IFs (which can become very difficult to debug & modify).

If you can post a copy of the workbook with representative data, I or someone else here might be able to suggest a better approach/solution.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-30-2011, 10:45 AM
JimS378 JimS378 is offline Nested IFs Windows 7 64bit Nested IFs Office 2003
Novice
Nested IFs
 
Join Date: Apr 2011
Posts: 5
JimS378 is on a distinguished road
Default Example file

The attached file contains dummy data and an example of the sheet I'd be working with. It also notes exactly what calculations are required.

Thanks again for your assistance.

Jim S
Attached Files
File Type: xls IF Example.xls (20.5 KB, 14 views)
Reply With Quote
  #6  
Old 04-30-2011, 11:14 PM
macropod's Avatar
macropod macropod is offline Nested IFs Windows 7 32bit Nested IFs Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Jim,

Try the attached.

As you'll see, I've inserted 3 columns with round & offset values that the handicap formula in I1 uses to work out which rows to draw the averages from. You can hide those columns if you don't want to see them.

Do note that, if you change the # of rows before the handicap table starts, the offsets will need to be adjusted accordingly.
Attached Files
File Type: xls IF Example.xls (22.5 KB, 25 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-01-2011, 06:40 AM
JimS378 JimS378 is offline Nested IFs Windows 7 64bit Nested IFs Office 2003
Novice
Nested IFs
 
Join Date: Apr 2011
Posts: 5
JimS378 is on a distinguished road
Smile

Macropod:

It'll take me several days to disect and try to understand your formula.
I certainly would not have come up with your solution.

Thanks very much for your help.

Jim S
Reply With Quote
  #8  
Old 05-03-2011, 08:20 PM
JimS378 JimS378 is offline Nested IFs Windows 7 64bit Nested IFs Office 2003
Novice
Nested IFs
 
Join Date: Apr 2011
Posts: 5
JimS378 is on a distinguished road
Thumbs up

Many thanks Macropod, all is well.
Case closed.

Jim S
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with nested cylinders and labels pixmanlajunta PowerPoint 0 01-18-2010 11:25 AM
Nested if formulat to merge cells cristi22 Excel 0 10-23-2006 08:28 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft