Solved

Excel Cross Tab IF Statements for Formatting

Posted on 2007-03-22
4
248 Views
Last Modified: 2008-02-01
I have a crosstab query from an access database that I am exporting to excel.  I have been trying to formulate the IF clauses for all cells.  My data contains the following:

Vertical Access = SOX Controls
Horitonal Access = Projects
1 = Control Tested
0 = Control Not Tested
(blank) = No Control Available

I want to run the IF statement to change a 1 to "Tested", 0 to "Not Tested" and (blank cells) to "N/A" and grey them out.

I am using the following for the cells:

=IF(A2=1,"Tested",IF(A2=0,"Not Tested","N/A"))

Can anyone please help??
0
Comment
Question by:davidkohne
  • 3
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 total points
ID: 18771793
=IF(A2=1,"Tested",IF(A2="","N/A","Not Tested"))

Then use Conditional Formatting to color the N/A cells.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18771853
BTW, if you are content to leave the blanks cells looking blank, you can make the 0 and 1 display as Not Tested and Tested, you could use this Customer number format:


[=1]"Tested";[=0]"Not Tested"
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18771929
"Customer number format" - what Customer is this? :^P
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18771996
ack!

custom number format :)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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