Nov 01 2023 08:27 AM
Hello,
I have an Excel spreadsheet that I am using to collect data. I am using formulas and in certain cells the formulas are working but in others they are not- #REF!. I have clicked on the icon to get further information and help resolving the error, but nothing has helped. Please help me with a solution to remove the #REF! errors from the spreadsheet. Thanks in advance!
Some working, but man are not.
Nov 01 2023 09:29 AM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Nov 02 2023 04:53 PM
I have uploaded a document. As you complete the cells moving to the right of the spreadsheet, the spreadsheet begins to not automatically use the inserted formula to calculate the numbers.
Nov 03 2023 06:23 AM
Let's take a look at column H (# of Students Passing 68-79%).
Cells H4 to H11 contain values that look like they have been entered manually.
But the cells from H12 down contain a COUNTIFS formula that refer to a range that cannot be found. Did this workbook originally contain more worksheets that have now been deleted?
Nov 06 2023 11:56 AM
Nov 06 2023 12:32 PM
Take N4. It contains the formula
=COUNTIFS(#REF!,1,#REF!,"a")
The first #REF! must originally have referred to a range elsewhere that contained 1 if a student needs to take the test.
And the second #REF! to a range that contained "a" if the student was absent.
These ranges are not present in the workbook, so I have no way to reconstruct the formula.
Nov 10 2023 06:46 AM