The most common analysis done across industries with the target vs actual. One of my client has a column chart where it displays the product-wise target vs actual, In which they can analysis which product is lagging and leading.

I have proceed with Stacked Column chart, for the same analysis, with more efficient way of understanding the lagging plant crisply.

Earlier the chart was displayed in the below format. Still i feel its not more effective.

ScreenHunter_16 Feb. 24 16.07.jpg

Note: In this scenario the list of products are static and not varying based upon the prompts.

List of Components used.

1.Play selector

2.Stacked Column chart

The data which is highlighted in yellow, are the actual & target data from the source.

ScreenHunter_16 Feb. 24 16.17.jpg

Assigned counter values for the play selector and inserted in the red cell.

ScreenHunter_16 Feb. 24 16.22.jpg

The difference between the actual & target is done in the row “E”. [=F4-D4]

Finally the target is calculated using the “MOD” function in the row “C”. “=IF(E4<0,0,(IF(MOD(G3,2)=1,0,E4)))”. Apply the logic in subsequent cells.

Make sure you send the play selector behind the chart “Send to back”. Now go to the preview mode you can see the target vs actual difference is blinking.

ScreenHunter_16 Feb. 24 16.31.jpg ScreenHunter_17 Feb. 24 16.31.jpg

Note : I have tried this scenario, with limited No.of Data, so that my excel formula didn’t hampered my performance of the dashboard.

We can use this technique for lot more option like highlighting some text in the dashboards. Can be used for alerts & warning too.

Regards,

Suman T

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Nameesh Jain

    Hi

    Thanks it is really usefull.

    could you please share the full version.. I mean how are you uisng the play selector and the final chart settings..

    (0) 

Leave a Reply