Excel formula issues

Question

Why does the latest version of Excel answer error ## when I add a blank cell with others which have imported numbers. My previous version, 2007 I think, performed this with no problems.    The cell I am adding with others is F23 in the formula below. This cell imports a result for another sheet. The other sheet scores the results of a number of sports games and I need to give  3 points for a win w, 1 point for a draw and 0 points for a loss. The formula is  =IF(F23="w",3,IF(F23="d",1,IF(F23="l",0,"")))   This formular still works on a score sheet developed in 2010 but if I copy it to the new score sheet it fails. Looking up the Excel help  I have tried various  suggested formulas including =IF(F23="",0,IF(F23="w",3,IF(F23="d",1,IF(F23="l",0,""")))) **=IF(F23="BLANK",BLANK,IF(F23="w",3,IF(F23="d",1,IF(F23="l",0,"""))))**without any success. Please help. Richard

Answer 1

Maybe this helps. The link below is the earlier document produced which still works with the IF formula. Cell J8 is read by J21, which contains the formula, and so on down and across. Cell Q8 adds J21 to J29 and updates as scores are entered. As you can see this document accepts a blank cell.

https://www.dropbox.com/s/tn5z5p14et2018a/1A%20Programmed%20Temp.xlsm?dl=0

Answer 2

eg..... Running Sheet cell B7 reads the result entered into Scores cell C4 (in this eg "d"). Running Sheet Cell E7 then reads cell B7 & has the IF formula  which converts it accordingly (1.5), Cell Z7 now adds E7 with the other results of Welly Black 1 & Welly Black 2 which  currently totals 15.

If you delete the entry in Scores Sheet cell C4  the result in Running Z7 goes to error (all cells in the Scores sheet need to be  be empty at the beginning of the Tournament). If you delete all entries in Scores all results go to error and do not update the Total Points round by round as results are entered.

Answer 3

I studied your sheet. Which cell should I look into to catch this problem?

Answer 4

Hello Richard

I am V. Arya, Independent Advisor, to work with you on this issue. Your formula is correct and it should work. I am not sure whether you want to refer to the previous sheet when you copy this formula into another worksheet. I would like to see your workbook.

May I request you to post the file with dummy data (no production / real data) to Onedrive or any other cloud sharing site and post the link here? If you need help about how to do - https://support.office.com/en-us/article/share-...

Hi V.Arya. Thank you very much for your offer of assistance, I am most hopeful you can help.

I don't use Onedrive as I find it very intrusive. The link below is on Dropbox so I hope you can download it. The documents are a work in progress and I discovered the issue when testing the triples Running Scores.

When games are completed the results are entered on the Scores sheet and progressively upload to the Running sheet and updated totals are available after each round of play.

By default the Tournament begins with empty Score sheets which is the cause of the issue.

I have not provided the older document as I have Copied & paste the formula into this doc. I can get you a link if you need.

https://www.dropbox.com/transfer/AAAAANQItI7WtxdknaUtcEZJb3pf0s9\_riz02on4ccdf8WNC2erEKms

Please let me know if you need anything else.

Thank you again.

Richard

Answer 5

Hello Richard

I am V. Arya, Independent Advisor, to work with you on this issue. Your formula is correct and it should work. I am not sure whether you want to refer to the previous sheet when you copy this formula into another worksheet. I would like to see your workbook.

May I request you to post the file with dummy data (no production / real data) to Onedrive or any other cloud sharing site and post the link here? If you need help about how to do - https://support.office.com/en-us/article/share-...