Excel sum formula problem

June 15, 2010 at 12:41:43
Specs: Windows XP
I have a SharePoint list linked to Excel Spreadsheet where I collect data to another spreadsheet. I have Yes and No answers that I need to give a value.
Example:
=IF(other spread sheet!A11="YES",".60",IF(other spread sheet!A11="NO","N",))
the formula is actually longer, but I cant get it sum the values in the current worksheet, the sum is always 0, several people have had the same issue, but I never see the answer.

Thanks, Matt


See More: Excel sum formula problem

Report •


#1
June 15, 2010 at 13:18:07
Hi,

Please post the full formula. If you go to the formula bar you can copy the formula and paste it into your reply.

Also what are you trying to SUM()
Are you trying to SUM the results of several of your IF() statments, or is it something else.

If the part of the formula you posted is accurate, can you say why you convert 'YES' to .60 and 'NO" to N

I also note that your conversion of 'YES' is to text and not to a value. You have the .60 in double quotes, which means that it is Text. The SUM() function does not make a conversion from text to a value when the text is in a range, which may explain a result of zero.

Regards


Report •

#2
June 17, 2010 at 17:11:53
=IF(other spread sheet
!A11="YES",".60",IF(other spread sheet!A11="NO","N",IF(other spread sheet!A11="PENDING","P",)))

The main problem is this is a huge spread sheet on another system.

My idea was to have a list in SharePoint linked to a spreadsheet that we could build a dashboard of sorts in.

We have 75 SOPs that have 25 metrics with differnet values and owners. we want to track the individually and in groups of owners and then have an overall view that shows the status of the system...

I will see if I can get it moved, so that you can see, thanks for your help...

Matt


Report •

#3
June 18, 2010 at 03:45:44
Hi,

As I mentioned before - your formula returns text "0.60" for Yes, and if you use SUM() on a range, these 'values' of "0.60" will be evaluated to zero.

Try this:
In cell A1 enter:

=IF(B1="","0.60",0.6)

In cell A2 enter:
=SUM(A1)

The result in cell A2 is 0
Enter any character in cell B1 and A2 returns 0.6

Regards


Report •

Related Solutions


Ask Question