SOLVED

cannot get formula to calculate correcly

%3CLINGO-SUB%20id%3D%22lingo-sub-1606476%22%20slang%3D%22en-US%22%3Ecannot%20get%20formula%20to%20calculate%20correcly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606476%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efirst%20post%20so%20hope%20all%20okay.%20Tell%20me%20if%20not%20and%20I%20change%20whats%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20have%20the%20following%20formula%20which%20should%20be%20adding%20figures%20from%20one%20sheet%20dependent%20upon%20the%20header%20and%20date%2C%20and%20inserting%20the%20monthly%20total%20onto%20another%20sheet%20under%20the%20month%20and%20correct%20header%2C%20except%20its%20not.%20From%20what%20I%20can%20evaluate%20its%20also%20adding%20the%20next%20months%20figure%20(doing%20April%2C%20would%20add%20May's%20accumulative%20Total%20to%20Aprils%7D%20also.%20I'm%20unable%20to%20work%20out%20why%20so%20am%20asking%20if%20anyone%20can%20see%20whats%20wrong%20firstly.%26nbsp%3B%20many%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1611830%22%20slang%3D%22en-US%22%3ERe%3A%20cannot%20get%20formula%20to%20calculate%20correcly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1611830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768120%22%20target%3D%22_blank%22%3E%40Icarium20%3C%2FA%3E%26nbsp%3B%20The%20problem%20is%20with%26nbsp%3BEOMONTH(%24B5%2C1).%26nbsp%3B%20Use%26nbsp%3BEOMONTH(%24B5%2C0)%20instead.%26nbsp%3B%20The%20second%20value%20is%20how%20many%20months%20to%20offset%20so%201%20means%20give%20the%20end%20of%20the%20month%20date%20for%20the%20month%201%20month%20after%20this%20date.%26nbsp%3B%200%20means%20this%20B5%20month.%3C%2FP%3E%3CP%3EBest%20of%20luck.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612890%22%20slang%3D%22en-US%22%3ERe%3A%20cannot%20get%20formula%20to%20calculate%20correcly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612890%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20mtarler.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works%20exactly%20as%20you%20said.%20Can%20now%20proceed%20to%20work%20out%20the%20Tax%20im%20due%20to%20pay%2C%20Doh%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello All

 

first post so hope all okay. Tell me if not and I change whats needed.

 

 I have the following formula which should be adding figures from one sheet dependent upon the header and date, and inserting the monthly total onto another sheet under the month and correct header, except its not. From what I can evaluate its also adding the next months figure (doing April, would add May's accumulative Total to Aprils} also. I'm unable to work out why so am asking if anyone can see whats wrong firstly.  many thanks

 

 

 

 

 

 

 

2 Replies
Highlighted
Best Response confirmed by Icarium20 (New Contributor)
Solution

@Icarium20  The problem is with EOMONTH($B5,1).  Use EOMONTH($B5,0) instead.  The second value is how many months to offset so 1 means give the end of the month date for the month 1 month after this date.  0 means this B5 month.

Best of luck.

Highlighted

 

Thank you mtarler.

 

That works exactly as you said. Can now proceed to work out the Tax im due to pay, Doh

 

Again thanks