Excel 2007 : #VALUE - Tracing problem cells?

Is it only dates in the range Other Receipts'!D3:D2050 ? i.e any text...

Does this change anything?

=SUMPRODUCT(--('Other Receipts'!D3:D2050>=DATEVALUE("1/1/2001")),--('Other Receipts'!D3:D2050<=DATEVALUE("31/1/2001")),'Other Receipts'!F3:F2050)

Another way to find out where the formula bombs is to check it with the Evaluate Formula tool (Tools - Formula Auditing - Evaluate formula). With 2000 values in the array, it may be a bit difficult, but you should be able to spot #Value! quickly!

Originally Posted by teylyn

With 2000 values in the array, it may be a bit difficult, but you should be able to spot #Value! quickly!

FWIW, "way back when" I put together the basics (with help from others) for analysing Sumproducts on a transactional basis...
The primary purpose of which was to act as a training tool (to elaborate as to how SUMPRODUCTs evaluate) and to ape Bob Phillips' white paper demo's but in "live" form... I also find this add-in can be useful in establishing rows generating errors etc...

http://www.excelforum.com/developmen...ct-matrix.html

(if you opt to look at it I would advocate using the last version listed)

Originally Posted by NBVC

Is it only dates in the range Other Receipts'!D3:D2050 ? i.e any text...

Does this change anything?

=SUMPRODUCT(--('Other Receipts'!D3:D2050>=DATEVALUE("1/1/2001")),--('Other Receipts'!D3:D2050<=DATEVALUE("31/1/2001")),'Other Receipts'!F3:F2050)

Yes and No, everything is a date with 3 exceptions, one cell is null and 2 cells are dates but as the dd is unknown they are listed as 00/01/2000. Again I don't think this is an issue as these 3 cells are run in the same formula that works for some months just not others.

I have also tried that exact formula you posted, still causes the same #VALUE! error.

Originally Posted by darkyam

Try =ISNUMBER(D1) and drag down and do the same for dates. Any that return false are text. Another way is to put 1 in any cell, copy it and paste special-->multiply over the currency and date columns. That should force them to become numbers.

Ok firstly I tried =ISNUMBER(F3-2xxx) Results were
4 FALSES where the currency was in dollars, checking the month and year against the original formula showed it wasn't affecting it, the formula was working where it was including these cells, furthermore I intend to update to £'s when I have the correct exchange amount.

I had a handful of FALSES where the entry was null (blank) again this included months/years where the formula worked so can't be that.

When trying on column D: which is Date

3 x FALSE for the pre-mentioned dates 2 starting 00/xx/xxxx and all the other FALSES were blank, again same conclusion as above. Formula seems unaffected when it includes these cells.

I'll come back to your other suggestion, as I need to duplicate my file incase I royally screw anything up first.

Originally Posted by teylyn

Another way to find out where the formula bombs is to check it with the Evaluate Formula tool (Tools - Formula Auditing - Evaluate formula). With 2000 values in the array, it may be a bit difficult, but you should be able to spot #Value! quickly!

Sorry I'm unfamiliar with this tool. I can get the tool to run but I don't understand the results. After clicking evaluate for abit "#VALUE!" is popping up in multiple places throughout the formula. I ran this in one of the cells where the formula wasn't working.

Originally Posted by DonkeyOte

FWIW, "way back when" I put together the basics (with help from others) for analysing Sumproducts on a transactional basis...
The primary purpose of which was to act as a training tool (to elaborate as to how SUMPRODUCTs evaluate) and to ape Bob Phillips' white paper demo's but in "live" form... I also find this add-in can be useful in establishing rows generating errors etc...

http://www.excelforum.com/developmen...ct-matrix.html

(if you opt to look at it I would advocate using the last version listed)

I'm going to try this now.

Edit: Ok tried it. i'm not sure what I'm looking at to be honest. However the easiest why for me to dechiper it was to run it on both a working and non-working cell. Basically the second result column is filled with #VALUE! however, this is both true on the working and non-working cell. In other words, there is no visible difference in the results of this add-on between a working and non-working cell, so now I'm thoroughly confused.

Please Login or Register  to view this content.

Not sure this will format correctly

cool! That's a valuable addition to my toolbox!!

I especially liked this bit in the thread:

Originally Posted by DonkeyOte

I am of good character and can be trusted...

I will look at this in more detail later... presently the board is pretty slow making doing anything quite difficult.

Given the nature of your calculation it should not matter if D contains non-numerics (no coercion of those values is taking place), same holds true of range F given the final range is not being explicitly coerced the non-numerics would simply be ignored.

Having looked at the output of the Analyser it does seem odd that the >= Jan 1 array works but <= Jan 31 does not given they're using the same range - ie you would expect both or neither to fail.

The only thing I would say is that you would get a #VALUE error if the date strings you used in the DATEVALUE function were not valid, eg "31/2/2003" etc...
the strings you enter should be appropriate to the regional settings on your client

Assuming the issue is not with the date strings... can you post a sample file which contains the data as stored on 'Other Receipts' - we need only the data as held in D & F - everything else can be cleared (ie nothing confidential - just dates and numbers).

On an aside another variant of your approach if always conducting monthly analysis would be:

Please Login or Register  to view this content.

the text conversion is regarded as being relatively slow but (and it's a big butt) it will still handle non-date values at source without throwing an error and is obviously a little easier to code - ie you need not concern yourself with assigning the correct date values - merely the correct MMYY string for the month in question.

(you would also get #VALUE! error using this method if arrays were of differing dimensions - not seemingly the case in this instance)

Originally Posted by DonkeyOte

On an aside another variant of your approach if always conducting monthly analysis would be:

Please Login or Register  to view this content.

the text conversion is regarded as being relatively slow but (and it's a big butt) it will still handle non-date values at source without throwing an error and is obviously a little easier to code - ie you need not concern yourself with assigning the correct date values - merely the correct MMYY string for the month in question.

(you would also get #VALUE! error using this method if arrays were of differing dimensions - not seemingly the case in this instance)

This seems like it's working, I'm just checking through it now to double check it is but I've tried multiple times replacing the error prone formula and it's working. Thank you, you're a star. I'll come back to this so I can re-read over and try to establish the initial problem.

Originally Posted by antispam246

I'll come back to this so I can re-read over and try to establish the initial problem.

If the revised version worked for you then that implies that either of or both of the date strings you were using within the DATEVALUE functions were invalid - ie could not be coerced - eg DATEVALUE("31/2/2003")
(in reality given the errors outlined that was the only cause that seemed possible)

Originally Posted by DonkeyOte

If the revised version worked for you then that implies that either of or both of the date strings you were using within the DATEVALUE functions were invalid - ie could not be coerced - eg DATEVALUE("31/2/2003")
(in reality given the errors outlined that was the only cause that seemed possible)

Thanks. Do you mind clarifying that "could not be coerced"? What would be the reason?

Also your formula works throughout, just finished checking. One minor glitch I've noticed though. How does this particular formula cope with null values? Specifically where the date cell is empty? One of the earliest dates and entries in my data has an empty date value and it looks like the formula has some how concluded that this empty cell date falls into 0100 (Janurary 2001) as it's including the amount within it. Technically the result from the formula should be £0.00 as there is no cell in that date range but it's including an empty cell with no date. This is the only cell with an empty date, I should really try to fill it but as I can't conclude it why would it add it into this particular formula?

The funny thing is, for this particular sum, if I use the old formula it works correctly (old as in the initial one i posted) which returns £0.00

Edit: To clarify

Please Login or Register  to view this content.

Results correctly with £0.00

Please Login or Register  to view this content.

Results in £11.97 which is the value of column F: in the no date row.

One minor glitch I've noticed though...

Sorry, my bad, if you have blanks then use MMYYYY format rather than MMYY
Blanks are 0 and in date terms that equates to 0 Jan 1900* , 0 Jan 1900 is of course 0100 in MMYY format as is 1 Jan 2000 but if you apply YYYY format you can differentiate (011900, 012000).

Do you mind clarifying that "could not be coerced"?

Dates in XL are Numbers - 27th Nov being 40144*
(time is decimal - ie noon is 0.5, 6am is 0.25, 6pm is 0.75)

The DATEVALUE function converts a date string to a date value - ie from text to number, so for ex. DATEVALUE("27-Nov-09") converts the string to a date (and thus a number).

It follows then that if the string is not a valid date the coercion will fail, eg DATEVALUE("31-Feb-2003") ... that date does not exist thus there is no date value associated with that string, in this instances this attempted coercion will generate #VALUE!

It is in essence the same as saying

=VALUE("10")

which converts the string "10" to the number 10, however,

=VALUE("Apple")

generates #VALUE! as "apple" can not be coerced to a number.

(*on 1900 date system)