coloring in Charts

**maximpinto** · 03-12-2010, 01:25 AM

Hi in attached excel
i am trying to define a graph

i want to be changed but nto able to do

how can i come up with a cchart which shows colors different for diff groups

like for CRM one color
Sadad one color etc

second requirement is

I want data to be displayed month wise
on horizontal axis i need one entry for jan but vertical it should show 2 values like for jan there is crm and sadad please help
thanks
max

**teylyn** · 03-12-2010, 01:54 AM

Hi maximpinto,

re-arrange your data like in the attached file and then create a clustered column chart.

**maximpinto** · 03-12-2010, 01:59 AM

Hi Teylyn,
Did u manually rearrange the data below or used any automatic way to do so please let me know

What i was thinking is when i have huge data i can get the valuese automatically in another sheet and then creat graph as you suggested
please help
thanks
max

**teylyn** · 03-12-2010, 03:02 AM

max, please don't quote whole posts, it's just clutter.

In the previously attached sheet, you can use this formula in B19 to populate the table

=INDEX($C$3:$C$14,MATCH($A19&B$18,INDEX($A$3:$A$14&$B$3:$B$14,0),0))

copy down and across.

**maximpinto** · 03-12-2010, 03:17 AM

Hi Teylyn,
Last one question if you dont mind

The same formula if i need to paste on another sheet in th same book say (sheet4)
at B19 then how does this formula modify to
i knwo we use the sheet name not able to get it

My intention is i will have raw data on one sheet and this modified data on another sheet with the graph what you suggested
thanks
maxim

**teylyn** · 03-12-2010, 03:30 AM

On Sheet 4

Column A has dates
Row 1 has categories CRM, SDAD, etc
B2 =INDEX('Channel Monthwise'!$C$3:$C$14,MATCH($A2&B$1,INDEX('Channel Monthwise'!$A$3:$A$14&'Channel Monthwise'!$B$3:$B$14,0),0))

see attached

**maximpinto** · 03-12-2010, 03:59 AM

Thanks a lot
Let me know how to close this post, this is a new design i guess

Also one last help, if data not exist it shows #N/A , can i see that instead of #N/A a zero appears so that it looks clean.

Thanks
maxim

**teylyn** · 03-12-2010, 04:23 AM

Max, for charting it is actually better to have N/A, otherwise, if you put data labels into the chart, it will show zero when there is no column. With N/A the chart will be cleaner. Also, the N/A is a "natural" result of the formula if there is no value found for the criteria. Trying to error-trap it and exchange the N/A for a 0 would be a waaaay much longer formula. Like this:

Code:

=if(isna(INDEX('Channel Monthwise'!$C$3:$C$14,MATCH($A2&B$1,INDEX('Channel Monthwise'!$A$3:$A$14&'Channel Monthwise'!$B$3:$B$14,0),0))),0,INDEX('Channel Monthwise'!$C$3:$C$14,MATCH($A2&B$1,INDEX('Channel Monthwise'!$A$3:$A$14&'Channel Monthwise'!$B$3:$B$14,0),0)))

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**maximpinto** · 03-12-2010, 05:49 AM

Thanks for your quick responses, was a friday and was finding it tuff to google for answers so posted here
was nice to get quick reply and solve my issue
thanks a lot
max