+ Reply to Thread
Results 1 to 4 of 4

Help with regards to a formula which has suddenly stopped working...

  1. #1
    Registered User
    Join Date
    05-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    2

    Help with regards to a formula which has suddenly stopped working...

    I have a bit of a problem gents which I can't suss out. Wondered if anyone could help me.

    I have a spreadsheet I use to track my bets. One of the worksheets is a calendar page that tracks the P&L for each day and then another column which sums the P&L for month and year.

    I had it working fine until a couple of days ago when for some reason it has stopped working and the month and year summaries are replaced with "#VALUE!" (the day to day works fine).

    This is the formula I have in the month summarising cells:

    =SUMPRODUCT(--(MONTH(Bets!B10:B1000000)=1),--(YEAR(Bets!B10:B1000000)=2012),Bets!AB10:AB1000000)

    Obviously the number and year changes depending on which month I need summarising.

    Now on the "Bets" worksheet in column B I have the dates of all the bets and in column AB I have the P&L. I have checked these columns and there seems to be no issue so I am really struggling to see why it has stopped working.

    Any help would be greatly appreciated.

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Help with regards to a formula which has suddenly stopped working...

    on the formulas ribbons, in the Formula auditing group, there is an "evaluate formula"....it litterally steps through the formula....this may help you see why your formula is not working....HTH
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Help with regards to a formula which has suddenly stopped working...

    Is it possible that some of your data is formatted as text instead of numeric data?
    Click on star (*) below if this helps

  4. #4
    Registered User
    Join Date
    05-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    2

    Re: Help with regards to a formula which has suddenly stopped working...

    Quote Originally Posted by judgeh59 View Post
    on the formulas ribbons, in the Formula auditing group, there is an "evaluate formula"....it litterally steps through the formula....this may help you see why your formula is not working....HTH
    Thanks for your replies. I think it seems like you might both be correct to an extent.

    I have used the evaluate formula and it says this : "A value used in the formula is of the wrong data type"

    I am unsure of what this means or what needs changing.

    I hope this makes sense to you guys as I am not great with Excel.

    Thanks very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1