Solved

Conditional Formatting in a pivot

Posted on 2014-12-09
4
77 Views
Last Modified: 2014-12-10
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
Comment
Question by:Seamus2626
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40489348
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
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40489382
Hi,

pls try

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

Regards
0
 

Author Closing Comment

by:Seamus2626
ID: 40491002
Thanks!
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40491018
Rgonzo - nice!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question