Handle "" Values in Numeric Calculations

Hi,

I am an experienced Excel programmer and find myself frustrated that I am unable to find an easy solution to this as it must be a common requirement. I have been drawing a blank (no pun intended) all night and have eventually decided that there are bound to be much smarter people out there that will have an elegant solution. This question applies to a roster spreadsheet I have developed which transforms data input into a visually attractive format in first normal form that I then subsequently use for various lookups using SUMPRODUCT and dynamically named ranges . . . but it can be very simply stated as below:

A B
1 Mon
2 Mon
5 Tue
="" =""
3 Tue

The data above would have been the result of formulae, and in row 4 I have tried to show that the data is actually equal to "" which obviously shows up as a blank cell. What I would like to do simply is to use SUMPRODUCT to ideally calculate say the sum of all values in column A when column B is equal to Tue.

If the data in row 4 was zeroes I could easily use =SUMPRODUCT((B1:B5="Tue")*(A1:A5)) to get 8. Instead as I have the blank ("") data in row 4, I will get a #VALUE error instead.

I have tried a number of ways to get the "" converted to a numeric zero for the formula calculation without any success. The N formula would be ideal and works like a charm on individual cells but it does not operate on a range.

Any help would be massively appreciated.

Regards
Ian

Re: Handle "" Values in Numeric Calculations

Hi Smallman,

I would love to post the original spreadsheet . . . but it as it is a roster system for work - with real names and staff numbers - I wouldn't imagine I would be particularly popular if I did that. It is also pretty large at 4MB. It is a rostering system for the department which has over 60 staff in 5 teams covering 24 x 7 hours. So . . . I have created a small worksheet with the essence of what I am trying to do.

You will notice that I can actually do a SUMPRODUCT on a mixed range (or at least ones with a "" in a cell) and SUMPRODUCT manages it quite elegantly and ignore the "" when it is merely summing - as does SUM. However as soon as I try and perform any calculations it fails as you point out. I do not mind using an alternative - and as a workaround I could read the data in and place a zero instead of a "" - but it is just not as elegant and I could potentially impact other calculations or comparisons in the worksheet.
[ATTACH=CONFIG]52704[/ATTACH]
Hope someone can suggest how to elegantly manage cells that have "" when trying to perform numeric calculations.

Cheers
lamking

Re: Handle "" Values in Numeric Calculations

I have enhanced the small spreadsheet to better show what I am trying to do in an elegant fashion. I essentially have created a helper column that checks for the "" value in a cell and replaces it with a numeric zero (0). I can then use SUMPRODUCT to check for the Day and add the value if the Day matches. I guess my question is is there an elegant way to do this without the helper column?

Hope someone can provide an answer? Please let me know if there is anything unclear about my line of questioning.

Cheers
lamking

[ATTACH=CONFIG]52707[/ATTACH]

Re: Handle "" Values in Numeric Calculations

Hi Smallman,

Yes you are right that would work but unfortunately you are also right about me oversimplifying it. The issue is that I actually have to manipulate the data in multiple columns - it is actually a date and time - extract the time and if it matches a criteria - then count it. So not a simply sum - maybe you could propose a solution if we were to use the existing spreadsheet but instead of a straight sum I needed to find the square of quantity for every day that was a Tue.

In the meantime I will try and create a more realistic spreadsheet.

Cheers
Ian

Re: Handle "" Values in Numeric Calculations

Okay,

Here is a more realistic spreadsheet - and one closer to the sort of thing I am doing in the full spreadsheet. I have a very small and simple spreasheet in the top half, I show how I can use the SUMPRODUCT to transform my first normal form roster records of staff and their shift times to a resource availability table per hour. This works as expected as long as all of the staff are working - where the staff are not working, no shift code is allocated and subsequent columns showing start and end times for their shift have had a "" filled in programmatically.

The lower half of the spreadsheet shows how everything breaks when I have a "" on which I try and do a numeric calculation. As I said above, this can be solved with a helper column, but I was trying to find an elegant solution of dealing with blank ("") data in columns which otherwise hold numeric data and wanting to perform numeric calculations on them.

Any help much appreciated.

Cheers
lamking

[ATTACH=CONFIG]52709[/ATTACH]

Re: Handle "" Values in Numeric Calculations

Hi Smallman,

I hope you get a chance to consider my earlier problem based on the expanded spreadsheet - but I was wondering if it is possible for members to direct message / email each other as I am sure I could gain some great insights by sharing the full spreadsheet directly with you. I am not trying to "not share" with other members and will share any lessons learnt on the forum, but the data could be construed as "sensitive" but the effort to desensitize it would be immense as it is fairly well and truly linked and automated.

I am sure I will minimize that exposure by emailing the spreadsheet to you directly and then you will understand my problem based on the exact data and not a watered down dilution.

Anyway appreciate your effort to date and your views on this suggestion.

Regards
Ian