• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting (histogram) based on calculation

N

Nergal_Perm

New Member
Good day to you, Excel ninjas!

Could you please help me with a CF issue. There's a problem:


Let's say we have some data arranged as time series, e.g.

Period 1 Period 2 ... Period n

Value 1 Value 2 ... Value n


I need to show the in-cell histogram showing the percent change between two adjacent periods, so that if Value1 = 100 and Value2 = 130, then the histogram in the Value2's cell should be filled by color by 30%. If the change is negative, then the cell color should be changed.


I've managed to do such a thing in a separate row, where I placed formulas, calculating the change between two periods, but I don't want to show this additional row in the final report.


Thanks a lot!
 
Nergal_Perm


Firstly, Welcome to the Chandoo.org Forums


Could you simply make the Rows Height = 0
 
Well, doing so will hide the CF fill also. My worksheet looks like this:


Period1 Period2 ... PeriodN

Value1 Value2 ... ValueN <- I want this row to have CF (histogram), according to next row values

Empty Change%1 ... Change%(N-1) <- this row has CF, according to this row values (histogram)


The problem that I can't solve is that CF(histogram) uses only current range values to compute the size and color of the filling in-cell rectangle, but I need to use next row values instead.
 
Nergal_Perm


Can you upload a sample file with an example of what your after

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Nergal_Perm


You have used Conditional Formatting, Data Bars, and although they give a great result cannot be used as you are attempting.


Unfortunately CF Formulas, cannot be applied to Data Bars.
 
Nergal_Perm


The good thing is that nearly every time I say something can't be done,

somebody pops up with a solution,


So, Fingers Crossed
 
Hi Nergal ,


What you can do is :


1. Put the percentage change formula in a row / column away from the values row / columns.


2. Use CF on the percentage change cells so that they display the bars.


3. Use the Camera Tool to capture these cells , and position the picture over the cells C4:I4.


4. Use the Custom Format of ;;; on the cells C4:I4 so that the values are not displayed.


Narayan
 
You must log in or register to reply here.
Back
Top