if,and ...what

[PivotMeThis]

I have a report that updates project progress weekly. I take this report and add it to a "master report" and create pivot tables to summarize the data.

There are 3 columns in if statements
Projects 10-20% Behind Schedule

=IF(AND(X2>=S2+10%,X2<S2+20%),1,"")

Projects Over 20% Behind Schedule

=IF(X2>=S2+20%,1,"")

Projects 80% or Less Completed and Over 20% Behind Schedule

=IF(AND(S2<=80%,X2>=S2+20%),1,"")

The 3 pivot tables I create reflect projects 10-20% behind schedule, projects over 20% behind schedule, and projects 80% or less complete and over 20% behind schedule.

Column S is “work completed” and column X is “time used”.

This seemed to be working okay but I recently noticed that if a project was 80% or less complete and over 20% behind schedule one week but caught up and is now only 20% behind schedule it still shows up on the pivot table that is was less than 80% complete (for that former week).

I would like projects to only show up on one pivot table at a time.

This seems really complicated to try to explain. I have a way of removing completed projects or projects that are no longer behind for any reason, but if a project is no longer behind on one pivot table (say it was over 20% behind last week, but it has moved to 10% this week) I only want to see it on the pivot table for projects that are 10-20% behind.

I'm really not sure if this is possible and that's why I'm here

 

[texasalynn]

Could you make one if statement to combine the three and then make the results 1, 2, 3 or something along those lines


<S2+20%),3,"")))[ p PHP]<S2+20%),3,??)))<>

 

[texasalynn]

formula

=IF(AND(S2 <=80%,X2 >=(S2+20%)),1,IF(X2 >=S2+20%,2,IF(AND(X2 >=S2+10%,X2 <S2+20%),3,"")))

 

[PivotMeThis]

This sounds like a great idea, then I could filter each pivot table on the number, 1, 2 or 3.

I will give this a try and report back tomorrow!

Thanks

 

[PivotMeThis]

Hmmm - I haven't done a nested if statement in a very long time and I am having trouble with this working. Can I combine if,and with if?

I have modified the above statement (which also didn't work) as follows:

=IF(AND(X2>=S2+10%,X2<S2+20%),1,IF(X2>=S2+20%),2,IF(AND(S2<=80%,X2>=S2+20%),3,"")))

I think the mistake is in the second part where it is just IF but I'm not really sure.

 

[PivotMeThis]

The first one worked, it just had some blank spaces in it when I copied it over. More info to come.

 

[PivotMeThis]

Thank you for the help. I got this working and added it to the existing macro so that I don't have to enter it every week. Works very nicely in the pivot tables too. Everything looks better and it's easier to put the tables together since I'm just changing the filter.

Thanks again!

 

[texasalynn]

Glad you got it working. Thanks for the feedback​