Graph Question

[evanesce]

Hello.
Well, I posted this somewhere at Naver.com, which is a very popular search portal in South Korea the other day. (I am Korean.)
I am not getting any answer yet.
I would greatly appreciate it if somebody can help me out.
Partial answers will also be highly appreciated if you don't know all of them.
(I spent more than 2.5 hours to post this. Drawing a graph and two tables is killing me. lol)
(I used white colored underscores to put some space in the table and graph.)

Here are two tables. (P stands for person.)

[Table1] - When high calrorie foods consumed (grams)


___Date__________P1_______P2_______P3_______P4_______P5
11-10-2011 _____+100____ +300_____+300______ 0 ______+500
11-09-2011 _____+530____ +663_____+232_____ +343 ____+242
11-08-2011 _____+360____ +443_____+321_____ +527 ____+415

[Table2] - When low calrorie foods consumed(grams)


___Date__________P1_______P2_______P3_______P4_______P5
11-10-2011 _____-300_____ -200_____-500____ -500 _____-100
11-09-2011 _____-530_____ -993_____-841______ 0 ______-321
11-08-2011 _____-332_______ 0 _____ -535 ___ -253 _____-332

Basically Table 1 and Table 2 show the chages in each one's weight measured at a particular time during a day.
Table 1 shows when each one had high calorie foods while Table 2 low calrorie foods.

[Question 1]

Let's assume that the volume of each box(■) shows 100 grams.
The red box indicates the increased portion, the blue one represents the decreased amount, the green one is the sum of the red and the blue.
It doesn't have to be those colors - red, blue and green. It's OK as long as they can be easily identifiable by any colors or patterns.

How can I draw a graph like this?

____ ____ ____ ______p1____ p2____ p3 ____ p4 ____ p5
______________500 ______________________________■

______________400 ______________________________■

______________300__________■______■_____________■

______________200__________■______■_____________■

______________100 ___■_____■______■______________■

_[2011-11-10]__ 0 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

_____________-100 ___■_____■______■_______■______■

_____________-200 ___■_____■______■_______■

_____________-300 ___■____________■_______■

_____________-400_________________■_______■

_____________-500_________________■_______■

[Question 2] I think the questions are pretty tough.

a) If I put the data in those two tables, I want the excel to automatically generate the graph for each date on each sheet. How can I do this?

b) I want to have a small calendar, so that I can choose a specific date to get a graph on that date. How can I do this? ( I guess this is the most difficult question...)

c) I want to display a year, month, and date like this -> ◀ 2011-11-10 ▶
Right arrow(▶) for the next date while left arrow(◀) for the previous date. If I choose a specific date, I want to get a graph for that date. What do I have to do?

[Question 3]

Is there any way I can see the green boxes only in the graph by clicking any button or whatever?

 

[Gerald Higgins]

Can you start by explaining what the colours mean ?
I think I understand them for P1.
But I don't understand them for P2 ? Why does P2 have 2 red squares, and 2 blue squares, and only 1 green square ? I understand why P2's squares range from +300 to -200.
Similar question for P3, P4 etc.

 

[evanesce]

Can you start by explaining what the colours mean ?
I think I understand them for P1.
But I don't understand them for P2 ? Why does P2 have 2 red squares, and 2 blue squares, and only 1 green square ? I understand why P2's squares range from +300 to -200.
Similar question for P3, P4 etc.

Thank you very much for your interest. I really appreciate it.

Let me explain the P2's case. (The same rule applies to the rest of them, which are P3, P4 and P5.)

There are two values for P2. One is +300 and the other is -200.

The color of +300 should be red to show the increased amount in P2's weight.

Likewise, the color of -200 should be blue to indicate the decreased amount in P2's weight.

Then, the P2's bar(dotted, boxed) graph should look like this.


____ ____ ____ ______ ____ p2____
______________500 _____________________________

______________400 ______________________________

______________300__________■______

______________200__________■______

______________100 ___ _____■______

_[2011-11-10]__ 0 - - - - - - - - - -

_____________-100 ___ _____■__
____
_____________-200 _____ ___■______

_____________-300 _______________

_____________-400_________________

_____________-500_______________

Now, add the two numbers together (+300 and -200).

Then, it gives +100 cancelling out unnecessary boxes - the two red boxes over 100 and the two blue boxes below zero.


____ ____ ____ ______ ____ p2____
______________500 _____________________________

______________400 ______________________________

______________300________________

______________200________________

______________100 ___ _____■______

_[2011-11-10]__ 0 - - - - - - - - - -

_____________-100 _____________

_____________-200 ______________

_____________-300 _______________

_____________-400_________________


I mean, I want everything to be shown in one graph - increased portion(RED), decreased potion(BLUE), and the netted portion(GREEN).

Likewise, P3's values are +300 and -500. Add them together. It gives -200.
Only -200 portion should be shown in green color in the graph to indicate the sum of the P3's two numbers.

And so on... ....

 

[Gerald Higgins]

OK I understand, thanks.

Is it important to have a chart with boxes like the one you posted ?
Or, would a stacked bar chart do instead ?
I have been able to produce a stacked bar chart which shows your data in a similar way to what you have shown.

 

[evanesce]

OK I understand, thanks.

Is it important to have a chart with boxes like the one you posted ?
Or, would a stacked bar chart do instead ?
I have been able to produce a stacked bar chart which shows your data in a similar way to what you have shown.

Any type of chart will do good.
Thank you.

 

[Gerald Higgins]

OK so here's what I did.

I made some assumptions about where your data is located.
Adapt as required to fit your actual locations.
High calorie, 10th Nov, is in the range B3:F3.
Low calorie, 10th Nov, is in the range B4:F4.

Not strictly required, but I put a calculation of the net value, in the range B5:F5, using this formula in B5 and then copied across

=B3+B4

I then set up some separate formulas to produce data to drive the chart.
In I3, copied across to M3:

=IF(B5<0,B3,B3-B5)

In I4, copied across to M4:

=IF(B5<0,B4-B5,B4)

In I5, copied across to M5:

=IF(B5<0,B5,0)

In I6, copied across to M6:

=IF(B5>0,B5,0)

Then, create a stacked column chart, with 4 series.
You might need to play around with the sequencing of the series.
Here's what I ended up with.
1st series - data from I6:M6, format this series as Green
2nd - I3:M3, format as Red
3rd - I5:M5, format as Green
4th - I4:M4, format as Blue

Try that, and tell us if it gives you the type of chart you want, and we can then look at enhancing it.

 

[evanesce]

OK so here's what I did.

I made some assumptions about where your data is located.
Adapt as required to fit your actual locations.
High calorie, 10th Nov, is in the range B3:F3.
Low calorie, 10th Nov, is in the range B4:F4.

Not strictly required, but I put a calculation of the net value, in the range B5:F5, using this formula in B5 and then copied across

=B3+B4

I then set up some separate formulas to produce data to drive the chart.
In I3, copied across to M3:

=IF(B5<0,B3,B3-B5)

In I4, copied across to M4:

=IF(B5<0,B4-B5,B4)

In I5, copied across to M5:

=IF(B5<0,B5,0)

In I6, copied across to M6:

=IF(B5>0,B5,0)

Then, create a stacked column chart, with 4 series.
You might need to play around with the sequencing of the series.
Here's what I ended up with.
1st series - data from I6:M6, format this series as Green
2nd - I3:M3, format as Red
3rd - I5:M5, format as Green
4th - I4:M4, format as Blue

Try that, and tell us if it gives you the type of chart you want, and we can then look at enhancing it.

When I followed your instrctions, the result was a little bit different from what I expecteted I mean, for example, for the case of P2, the green box was located at the top of the graph, which is between 200 and 300.
But I was able to get a very good idea from your answer.

By the way, any suggestion or idea for the question 2 and 3?

Once again, your idea was very helpful.
I can't thank you enough.
I really appreciate it.

Jae.

 

[Gerald Higgins]

If the green box is at the top, play around with the sequence of each data series. The method for re-sequencing data series is different in different versions of Excel. I've just moved from 2003 to 2007, and this method seems to have changed. But you can definitely do it in both versions.
If you're having trouble getting that to work, post back and tell us which version of Excel you're on, and I'll talk you through it.

I've got some ideas for Qs 2&3, but I'd prefer to finish off No.1 first if that's OK

 

[evanesce]

If the green box is at the top, play around with the sequence of each data series. The method for re-sequencing data series is different in different versions of Excel. I've just moved from 2003 to 2007, and this method seems to have changed. But you can definitely do it in both versions.
If you're having trouble getting that to work, post back and tell us which version of Excel you're on, and I'll talk you through it.

I've got some ideas for Qs 2&3, but I'd prefer to finish off No.1 first if that's OK

Hello.
I am using Excel 2007.
I have been trying to figure it out but I still don't know.
Even a slight hint will be very appreciated.
Thank you.

 

[mgm05267]

Hi evanesce,

I have a solution for this...

How can I send you the file

You have any email ID...

Regards,

MGM