Waterfall chart to show budget vs Actual

[jp16]

Hi, i am trying to reconcile the difference between Budgeted Net Profit vs Actual Net Profit.
However, i have problem doing up the Series. I do not know how to do it.

I need 1 series for the Budget Net Profit, 1 series for the Actual Net profit. (as my mgmt required them to to be different colour)

Then i aso need 1 Red Series and 1 Green Series

Red Series = Actual Revenue < Budgeted Revenue and Actual Expenese > Budgeted Expenses
Then the rest is green.

My data series is in G1:M10

I have difficult in doing it.

Hope someone can help

[HansDouwe]

You are starting a new thread again?

Over the past 7 days, in 3 different threads you started, I have not received any response from you to the solutions I provided for you.
And I'm not the only one you've been ignoring.

I therefore assume that you do not appreciate my contributions and will therefore no longer answer a new thread of yours.

It is very annoying that when I voluntarily invest my valuable time to solve your Excel problems, I (and not only I) do not receive a response from you.

Below are 2 of the 3 links from recent threads from you on which I have not received a response from you.
https://www.excelforum.com/excel-for...-on-count.html and
https://www.excelforum.com/excel-for...from-home.html

[jp16]

You are starting a new thread again?

Over the past 7 days, in 3 different threads you started, I have not received any response from you to the solutions I provided for you.
And I'm not the only one you've been ignoring.

I therefore assume that you do not appreciate my contributions and will therefore no longer answer a new thread of yours.

It is very annoying that when I voluntarily invest my valuable time to solve your Excel problems, I (and not only I) do not receive a response from you.

Below are 2 of the 3 links from recent threads from you on which I have not received a response from you.
https://www.excelforum.com/excel-for...-on-count.html and
https://www.excelforum.com/excel-for...from-home.html

Hi, sorry for not replying, not sure if you get my brother's pm. Those unresponsed thread is mine, not his, i am not able to reply as i am overseas recently. Hope you can help him.

Thanks.

[jp16]

Hi, can anybody help with this?

[HansDouwe]

i am not able to reply ... ?

If you can post this, you can also reply the other threads.

[MrShorty]

I'm having trouble understanding what you want to do. About the only observation I can make is that your description of the desired logic in column N is opposite from the logic that is programmed into column K and L. For example, in row 5, column N says that you want the absolute value of column E to end up in the "Red" column (column L) when "Actual" (column D) is greater than "Budget" (column C) -- or, since column E is subtracting column C from column D, when column E is greater than 0, you want column E to end up in the "red" column when column E is greater than 0. When column E is less than 0, you want it to end up in the "green" column. However, the formulas in columns K and L are doing just the opposite. When E is greater than 0 (when "Actual" is greater than "Budget"), it is putting the value of E into the "Red" column. It ends up in the "Green" column when E is less than 0 (when "Actual" is less than "Budget").

I'm wondering if it is as simple as flipping the logic. Whether that is switching the Red/Green labels in K1:L1 or changing the formulas in columns K and L. Or maybe the problem is more complex, and I cannot see it.

In any case, help us better understand what you want to be different, and we should be able to help.

[jp16]

Hi, something like that. I use shape to draw something i need.

[MrShorty]

There's not a lot of new explanation, but I think I can understand from the picture. Here's what I did:

1) The red/green logic in columns K and L still seems backward to me. I change K1 to be "Red," and L1 to be "Green."
2) The values in column J that calculate the dummy "base" do not look correct for the desired chart values. The value in J3 looks correct, but J4 on do not. I replace the formula in J4 with =J3+L3-K4 (copy/paste/fill down to J9).
3) The NA errors in columns K and L are creating a problem, so I replace the NA() with 0 in the formulas in columns K and L.
4) Now the numbers look right, so I create the chart. Select G1:L10 -> Insert -> Chart -> Stacked column.
5) Format the data series to have the desired colors, and any other chart elements, as desired.

That should be close to what you've drawn in your picture.

[jp16]

Hi, try ur step, somehow work. Just need to do some touch up.

Btw, i realise there is a default Waterfall chart in Excel, can that acheieve what i wan? I dont think the default waterfall is able to Up=Green, down=Red right?

[MrShorty]

My older version doesn't support the waterfall specialty chart, so I don't know.

[jp16]

Oh. Thanks for your help.