Bob Marshall
Blogger
0
2861

# EXCEL 2010 Display of KPI data in a Matrix

EXCEL 2010 Display of KPI data in a Matrix

• ### FRV Limited Cost Trader: defining Avertising Costs

I am trying to produce a reusable  report that includes an indicator in a particular cell based on two numbers.

I have a block of cells in a, say, 6x6 matrix and would like to show specific character (x or dingbat) in the matrix; the position in the matrix based on two values in another part of the spreadsheet.

The logic would be along the lines of "Display ? in cell( a25, offset up(value in cell f23), offset across( value in cell l19))"

I can do this by placing an if  statement in every cell in the matrix but wondered if there is an easier way to do this with a single function. The matrix would be replicated in various sections of the report and the 'if' approach would require an edit of every cell in the matrix

Many Thanks

Bob

 ? UP Across 5.00 4.00

### Replies

09th Nov 2011 13:24

Can't think of a simpler way

without using vba. Can you not construct the spreadsheet initially so you can just cut and paste the formulae across the various matrices and allow the relative referencing to sort it out; you can always move your source cells subsequently to where they are wanted?

Having said that someone will be along in a moment with the obvious solution.  :-)

Thanks (1)
By shurst
11th Nov 2011 22:57

Matrix

Sorry if I've misunderstood but it's late! Would the following do what you want: enter the required symbol in every cell of the matrix. Change the font colour to white, so that they all become invisible, then use a conditional format formula such as:

=AND(ROW()=\$F\$19,COLUMN()=\$F\$20)

(Obviously adjusting to allow for the offset from row1, column1 for the starting position of the grid)

set the format to black or red or whatever font colour. Only the cell where the row and column numbers match the cell values should then be visible.

Thanks (1)
to tom123
18th Nov 2011 22:08

Thank you Simon, it works perfectly.

Bob

Thanks (0)
By JC
19th Nov 2011 07:36

Heat Map for large amounts of data ...

Alternatively ..

Heat maps allow lots of objects (KPI's, shares etc) to be easliy identified in order of importance or weighting

See

http://how.best-free-information.com/2009/04/how-to-create-a-heat-map-in-excel/

http://bitesizebio.com/articles/how-to-create-a-heatmap-in-excel/

http://blog.xlcubed.com/2008/08/heatmap-tables-with-excel/

Also use a google search of 'excel heat map'

Thanks (1)
By shurst
19th Nov 2011 09:06

Conditional formatting

Thanks for letting us know that it worked.

Interesting links re the heat maps. Excel conditional formatting in 2007 and 2010 also includes the 'Colour scales' option which allows for simple cell-based heat maps.

Thanks (0)