Nested IF, AND OR Null Statement Formula Help Required

**anniem333** · 06-18-2015, 07:37 PM

Hello Forum,

I have a problem that I hope you'll be able to assist me with. I've been pulling hair out for hours over this one. :-(

I 'm currently setting up a workbook that various staff members, will be expected to populate with annual data. Most of the calculations and other IF statements work without error, but I have one column of data that is dependant on other having an entry greater than zero.

Example: If cell A2 is = 10%, then cells B2, C2, and D2 must be populated, with this total calculated in cell E2, and which must be equal to 100%. If E2 does not equal 100%, then it needs to indicate what score it is showing.

If cell A2 is blank, then cell E2 (the total cell) will equal 0, however, if cell A2 is >0, and cells B2 to D2 are blank cell E2 needs to indicate that those cells need to be populated.

I've been trying variations of the following, but nothing gives me quite what I want or I get a Name or Value error message.

=IF(A5>0,AND(ISBLANK(B5:F5)),"Complete Data",IF(A5>0,IF(SUM(B5:F5)=100,100,SUM(B5:F5))))

So, what I'm trying to say in my formula is if A5 is greater than zero, and cells B5 to F5 are blank, show the message, "complete data," but if A5 is greater than zero and B5 to F5 are not blank, then sum the data in B5 to F5, if it is equal to 100, show 100, if not, show me what the sum is. If there is no data in A5, there will be nothing in B5 to F5, so show me a zero in the total cell. The 'total' cell is where this formula is sitting.

If anyone can assist me or has a more efficient way of dealing with such a conundrum I'd be most grateful.

Thanks for your attention.

Annie

**benishiryo** · 06-18-2015, 07:59 PM

welcome to the forum, annie. not sure if i fully understand. you showed a 10% eg in A2. so is your data in B:D also a percentage? if so, will E2 show 100 or 100%? also, does it matter to show a different calculation whether column B:D adds up to 100 or not. a SUM(B2:D2) will give you the answer right? if it's 50, it gives you 50. if it's 100, it gives you 100. if i am right, then maybe:
=IF(AND(A5>0,COUNT(B5:D5)=3),SUM(B5:D5),"Complete Data")

if not, it would be great if you can upload a sample excel file & also manually key in the results for a clearer picture. To upload, Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
and to illustrate your example better, click on How To Get Quick & Good Answers

**anniem333** · 06-18-2015, 08:17 PM

Hi Benishiryo,

Thanks for replying.

I've attached a file, which might illustrate my problem more clearly. If there is data in the "Other" column, then you have to account for 100% (where using integers to represent percentages) of the time across the columns headed B2 to F2. If there is no data in the "Other" column, then the total in column G should read 0 (zero), if there is data in the "Other" column, then data in columns B to F must not be blank.

I hope this helps.

Annie