• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

Conditional Formatting in a pivot

I have data in a pivot

Name   Jan Feb etc etc Sep  Oct Nov

Dave      1      0     1   1    1     1      1


I am looking for conditional formatting that will turn a row red if two out of the last three months have a value of 1.

Thanks
0
Seamus2626
Asked:
Seamus2626
  • 2
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
A bit hard to answer the question, given the lack of specifics.

However, here goes. If your pivot values starts at E3, then this formula will give you the total of the last three months:

=INDEX(E3:IV3,0,COUNT(E3:IV3)-2)+INDEX(E3:IV3,0,COUNT(E3:IV3)-1)+INDEX(E3:IV3,0,COUNT(E3:IV3))

Therefore, you could put =2 or >=2 to that to get your condition.
0
 
Rgonzo1971Commented:
Hi,

pls try

=SUM(OFFSET(B5:IV5,0,COUNT(B5:IV5)-3,1,3))>=2

Regards
0
 
Seamus2626Author Commented:
Thanks!
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Rgonzo - nice!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now