Trying to get field to recognize zeros but not Blanks

I have a large data set of about 70 rows and 150 columns. Rows are client names and columns are week dates going back to jan 2018. Each cell has a # in it. Some of these numbers are zeros and some are blank because that week hasnt happened yet (eg 2020). I also have a graph that has a dropdown associated with it. I want the formula to pull the zeros as zeros, but i want it to pull the blanks as #N/A so the line graph will ignore them when it plots it out. I know excel recognizes blanks as zeros so I am stumped as to how to figure this out. Please help!! I've tried different combinations of isblank and it just wont work. I'm no expert here so in a little over my head.

UPLOADED SAMPLE

Column B = sample client names
Row 3 = dates
Field = raw data
Row 86 = the results I'm looking to pull. I want it to pull zeros if there is a true zero in the field, but if there is a blank, i want it to pull #N/A.

C91 is the drop down selection for the "client", in this case just #s.

Hi and welcome to the forum

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Try the following formula in cell D86 and across: =IF(ISNUMBER(INDEX(D4:D76,MATCH($C91,$B4:$B76,0))),INDEX(D4:D76,MATCH($C91,$B4:$B76,0)),NA())
Let us know if you have any questions.

Hi,

If you're not too far committed to your current data layout then it would be preferable if you arranged the data in a normalised regular 2 dimensional range.
i.e create columns for

Date
Client Name
City
Wins

and add a new row record row for every non zero win.

Then any and all sorts of analysis and summarisation is available to you when you use a Pivot Table with some field Slicers to filter the data. Then you avoid the need for formulae altogether.

I've attached an example where I've created a new set of Data and populated it with the first 5 weeks of data just to give you an idea. I've added the Pivot Table and an associated Pivot column chart which changes as you change the PT filters with the slicers.

You can do a lot with Pivot tables. This is just one example but other statistics can be derived just by changing the Wins Value setting in the PT field list.

I believe that Richard has a good suggestion, however I will go ahead and respond to post #6.
Assuming that all values will be placed in a column at the same time try the following for H97: =IF(E4="",NA(),SUMPRODUCT(($C4:$C76=$F100)*(E4:E76)))
Notice that after copying to formula over to EG97 some of the cells will display #VALUE errors. I tracked down the value error for cell I97 which is reporting on the week of 7/15/2018. The error results from the zero in cell F17 being text. You can readily identify text values mixed into the data set when you remove the "center" formatting. The actual numbers will display on the right side of the cell while the text will display on the left. In the future you may want to enter data into rows 4:76 without formatting to avoid having to go back through and edit.
Let us know if you have any questions.

I feel as if I need to see the current set up of the graph and drop downs. I will also say that there are contributors whom are better at graphs than I, so another option might be to mark this thread as 'Solved' (using the thread tools menu at the top of the page), and open a new thread in the Excel Charting & Pivots forum that specifically addresses graphing between dates.
Let us know if you have any questions.