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

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

textand 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

=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

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.6Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History