Retain final figure as month changes.

[IanB76]

Hi,

I have a formula that reads from a cell based on the month we are in. the cell it reads from is a constantly changing figure throughout the course of the month.


=IF(C3=MONTH(TODAY()), $C$16)

The formula works perfectly fine, however, the problem lies when we move to the next month, the figures will become false for September.

I need to somehow retain the last figure of the month so the chart that runs off this doesn't keep zeroing itself.

[TABLE="width: 838"]
<colgroup><col><col><col span="10"><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 774"]
<colgroup><col><col span="10"><col></colgroup><tbody>[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


I keep ending up with circular references or just a broken formula in various different ways.


Any help is greatly appreciated

 

[FormR]

Hi, it's not all that clear to me at least but maybe:

=IF(C3<=MONTH(TODAY()), $I$16)

 

[IanB76]

Hi, it's not all that clear to me at least but maybe:

=IF(C3<=MONTH(TODAY()), $I$16)

Thanks for the quick reply


Although that formula will work, come the 1st Oct the figure in cell $i$16 is going to change again which will give the wrong reading.

If (for arguments sake) it was the 30th September today, the figures under "September" "0,7,0" are the last figures calculated by the formula for that day, those three numbers are what I want to stay when midnight comes and we move into October, the next column along will then start the running the formula again over the course of October and then so on throughout the year.

In turn this table is plotting a chart displaying the peaks/dips from the final day of each month.


Does that make more sense?


Cheers

 

[FormR]

Hi, don't you have the data within the spreadsheet to calculate each month individually? If not then I guess you'd need to convert each months formulas to values - the timing and triggering of which might prove problematic.

 

[IanB76]

Hi, don't you have the data within the spreadsheet to calculate each month individually? If not then I guess you'd need to convert each months formulas to values - the timing and triggering of which might prove problematic.

Hi,

No, the cell it is reading from is itself reading from cells across other worksheets in the workbook to gather the information. It's the table I am trying to get to capture that final figure and plot the graph.

I was wondering if I was going to need a VBA code and possibly attach it to a button to run the calculation manually on a daily basis.




Thanks for your time


If you know of any links to the sort of code I am looking for , be a big help.



Cheers

 

[FormR]

Hi, it's very difficult to make any suggestions as I don't really know what those daily calculations are.

Do the other worksheets not contain data that includes a date that can be used to build your summarised plot data on a month by month basis?

 

[IanB76]

Hi, it's very difficult to make any suggestions as I don't really know what those daily calculations are.

Do the other worksheets not contain data that includes a date that can be used to build your summarised plot data on a month by month basis?

Okay, pulling the data from another worksheet (I am not 100% convinced it will work in the correct fashion but is a building block to work from) I have come up with this formula.



=SUMIFS(Required!Q11:Q842, Required!E11:E842,"MONTH(9)", Required!I11:I842,"<>",Required!N11:N842,"Complete", Required!P11:P842,"<>" )+($F$16)


Only this is returning a value of "0" where as it should be "7"


The formula in "F16" was : =SUMPRODUCT(--(TRIM(F18:F114)<>""))+(Required!N5) but is now : =SUMPRODUCT(--(TRIM(F18:F114)<>""))


The formula in "N5" that i have removed from the "F16" formula is the beginning of the first formula without the month section. (as shown bellow in the second half of the formula)

=COUNT(' Log'!H4:H808)+SUMIFS(Q11:Q842,P11:P842,"<>",I11:I842,"<>")


So I am (effectivily) doing the exact same formula as above only using column "E" which holds a date and then adding "F16" on at the end.



The answer to the original sum in N5 is "7" which should be the answer i don't seem to be getting.




Does this help?



Cheers

 

[FormR]

The formula in "N5" that i have removed from the "F16" formula is the beginning of the first formula without the month section. (as shown bellow in the second half of the formula)

=COUNT(' Log'!H4:H808)+SUMIFS(Q11:Q842,P11:P842,"<>",I11:I842,"<>")

I think you have two extra conditions in the new SUMIFS() - so along with the month section you are also checking that N11:N842 have the text "Complete"

I think you need to check that E11:E842 has the exact text "MONTH(9)" and N11:N842 has the exact text "Complete" - i.e. there are no hidden spaces, the spellings are exact etc.

 

[IanB76]

I think this is where it is not working


The month date in column "E" is in the format as listed below, it's not text (I did remove the "" from the formula when i saw I had put them in)


[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD]18/08/2017[/TD]
[/TR]
[TR]
[TD]30/08/2017[/TD]
[/TR]
[TR]
[TD]02/09/2017[/TD]
[/TR]
[TR]
[TD]23/08/2017[/TD]
[/TR]
[TR]
[TD]25/08/2017[/TD]
[/TR]
[TR]
[TD]26/08/2017[/TD]
[/TR]
</tbody>[/TABLE]



=SUMIFS(Required!Q11:Q842, Required!E11:E842,MONTH(9), Required!I11:I842,"<>",Required!N11:N842,"Complete", Required!P11:P842,"<>" )+($F$16)


So i need the month part of that formula to read the date in the list selecting only the month from it.





Cheers

 

[FormR]

So i need the month part of that formula to read the date in the list selecting only the month from it.

For the current year you could do something like this:

=SUMIFS(Required!Q11:Q842, Required!E11:E842,">=" & DATE(YEAR(TODAY()),9,1), Required!E11:E842,"<" & DATE(YEAR(TODAY()),10,1),Required!I11:I842,"<>",Required!N11:N842,"Complete", Required!P11:P842,"<>" )+($F$16)

i.e. greater than or equal to the 1st September and less that the 1st October.