Replies

In cell G3 place this formula.

=SUM(IF((Sheet2!$A$2:$A$44=A3)*(Sheet2!$B$2:$B$44=”8″),Sheet2!$C$2:$C$44))

use Ctrl-Shift-Enter to place it because it is an array formula. Copy it down and that should do it for you.

yoyoPHIL

Steve;

I believe you will get a glitch when it’s done that way. If you were to have a day, say Jun-03 that is 0, then Jun-04 is a positive value, then Jun-05 is a 0 again, plotting the positive value will surely entail losing the drop to zero feature. The way I see around this is instead of a high-low entry, try a in-constant-out set up. See my reworked attachment.
I used your thinking to create formulas that look at the results and select needed values for the in and out points with the constant being the dates value. With this set-up we at least get a line plotted for positive results which have a 0 before and after them.

That said, I can’t believe I spent so much time on this puzzle when the original poster hasn’t even replied back. Always like your work Steve. Thanks

yoyoPHIL

Steve;

I believe you will get a glitch when it’s done that way. If you were to have a day, say Jun-03 that is 0, then Jun-04 is a positive value, then Jun-05 is a 0 again, plotting the positive value will surely entail losing the drop to zero feature. The way I see around this is instead of a high-low entry, try a in-constant-out set up. See my reworked attachment.
I used your thinking to create formulas that look at the results and select needed values for the in and out points with the constant being the dates value. With this set-up we at least get a line plotted for positive results which have a 0 before and after them.

That said, I can’t believe I spent so much time on this puzzle when the original poster hasn’t even replied back. Always like your work Steve. Thanks

yoyoPHIL

Without an example it it difficult to understand exactly what you are looking for. None-the-less…I believe that on an area chart Excel takes and plots the number/value for the time increment. You are saying you want excel to plot the value and also zero/nothing (drop to zero) all in the same time frame. I don’t think that can be done using the area chart.
I have attached a file which plots values across a time frame of ten days using the area chart and also a column chart which was set to a gap width of zero. Maybe this will work for what you need.

yoyoPHIL

Without an example it it difficult to understand exactly what you are looking for. None-the-less…I believe that on an area chart Excel takes and plots the number/value for the time increment. You are saying you want excel to plot the value and also zero/nothing (drop to zero) all in the same time frame. I don’t think that can be done using the area chart.
I have attached a file which plots values across a time frame of ten days using the area chart and also a column chart which was set to a gap width of zero. Maybe this will work for what you need.

yoyoPHIL

I was hoping you were going to post a copy of your Stock Spreadsheet so everyone at Woody’s could use it to make some extra money. Not even a stock tip…………wheres the appreciation?

yoyophil

I was hoping you were going to post a copy of your Stock Spreadsheet so everyone at Woody’s could use it to make some extra money. Not even a stock tip…………wheres the appreciation?

yoyophil

You could use the line tool to place a diagonal line in the cell(s). The line sorts with the cell as requested. See attached.

yoyophil

You could use the line tool to place a diagonal line in the cell(s). The line sorts with the cell as requested. See attached.

yoyophil

Sorry about the wrong range with some of my formulas. I have attached a copy of your spreadsheet with the formulas as requested. I have also adjusted some others so errors are not shown when the sheet is cleared of any entries. I also used the macro recorder to give you a CLEAR button which removes the entries so you delete old entries and start on the next month.

yoyophil

Sorry about the wrong range with some of my formulas. I have attached a copy of your spreadsheet with the formulas as requested. I have also adjusted some others so errors are not shown when the sheet is cleared of any entries. I also used the macro recorder to give you a CLEAR button which removes the entries so you delete old entries and start on the next month.

yoyophil

After using Steve’s fixes add these.

D38 – =COUNTIF(D6:D36,”>=90″)

D39 – =COUNTIF(D6:D36,”<=32")

D40 – =COUNTIF(D6:D36,"<=0")

I38 – =COUNTIF(H6:H36,".10″)-COUNTIF(H6:H36,”>.50″)

I40 – =COUNTIF(H6:H36,”>.50″)

L38 – =SUM(IF((J6:J36=”Y”),K6:K36)) this is an array formula, Ctrl-Alt-Enter to when entering it.

L39 – =COUNTIF(J6:J36,”Y”)

L40 – =OFFSET(L6,MATCH(L44,L6:L36,0)-1,-1,1,1)

I am pretty sure these should do it for you.

yoyophil

After using Steve’s fixes add these.

D38 – =COUNTIF(D6:D36,”>=90″)

D39 – =COUNTIF(D6:D36,”<=32")

D40 – =COUNTIF(D6:D36,"<=0")

I38 – =COUNTIF(H6:H36,".10″)-COUNTIF(H6:H36,”>.50″)

I40 – =COUNTIF(H6:H36,”>.50″)

L38 – =SUM(IF((J6:J36=”Y”),K6:K36)) this is an array formula, Ctrl-Alt-Enter to when entering it.

L39 – =COUNTIF(J6:J36,”Y”)

L40 – =OFFSET(L6,MATCH(L44,L6:L36,0)-1,-1,1,1)

I am pretty sure these should do it for you.

yoyophil

In conditional formatting use Cell value is / equal to / Behind
and click ok.

If you want to be sure it works place it in a cell which has Behind in it.

To copy this to other cells (those you want in column F) click on the cell you placed the conditional formatting in, copy, then select the cells you wish the formatting to go into and select paste special / formats. That should do it.

yoyophil

In conditional formatting use Cell value is / equal to / Behind
and click ok.

If you want to be sure it works place it in a cell which has Behind in it.

To copy this to other cells (those you want in column F) click on the cell you placed the conditional formatting in, copy, then select the cells you wish the formatting to go into and select paste special / formats. That should do it.

yoyophil