Change N/A Cells to 0

Resolved · Urgent Priority · Version 2016

Thu 5 Sep 2019, 13:28 Delegate Philippe said...

Philippe has attended:
Excel Advanced course
Excel VBA Introduction course

Change N/A Cells to 0

Hello,

I have some cells with the following formula :
=INDEX($A$3:$V$3;EQUIV(C7;$A$2:$V$2;0))

They produce a result #N/A
How can I make sure that the result is 0 as opposed to #N/A please?

Thank you.
Kind Regards,

Philippe

Thu 5 Sep 2019, 13:50 Trainer Jens said...

RE: Change N/A Cells to 0

Hi Philippe,

Thank you for the forum question.

Try:

=SIERREUR(INDEX($A$3:$V$3;EQUIV(C7;$A$2:$V$2;0)),0)

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 5 Sep 2019, 13:59 Delegate Philippe said...

RE: Change N/A Cells to 0

Great. Thank you Jens.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Display pictures on Chart Data Point

Replacing a single chart data point bar with a picture.
Step 1: Left click on a bar. Then, wait, and do a second single click on the bar. This will select just one data point.

Step 2: Right click on the bar and select Format Data Point.

Step 3: On the fill effects tab, choose a picture. Browse for a picture for that bar. Indicate if you want it to be stretched or stacked. Repeat for each bar.

View all Excel hints and tips