Returning #NUM! Error in formula

sue.Caradonna

New Member
Joined
Jan 17, 2005
Messages
8
I have a spreadsheet with the following formula which returns information from a database. However, if the "S3" and "T3" gives in a "0" result from the database, the function returns #NUM!. I tried to get rid of the "#NUM! by adding the following If(ISNA(Error.TYPE etc code but it now returns "0" result even if the result calculates data.

{=MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday,IF(ISNA(ERROR.TYPE(AgeToday)),0,IF(ERROR.TYPE(AgeToday)=6,1,0)))))))}

Can you help me amend the formula to show the correct result if the formula returns a value and also a "0" if the formula results in 0 is no result is available.

Hope this makes sense.

Many thanks for your help. Its greatly appreciated
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Try changing it to:
=IF(or(S3=0,T3=0),0,MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday)))

Enter with Shift-Ctrl-Enter
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Or even better:

=IF(ISERROR(MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday))),0,MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday)))
 

sue.Caradonna

New Member
Joined
Jan 17, 2005
Messages
8
Your answer gives the correct result when there is a result to be shown, but if T3 and S3 do not result in a "median number" because there are none in the T3 and S3 categories, then its still return #NUM!. Any further clues
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

Did you try the second formula (entered with Shift-Ctrl-Enter). It should never return #num.

Here it is again:
=IF(ISERROR(MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday))),0,MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday)))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Also...

=IF(SUM((Team=$S$3)*(JobClassification=$T$3)),MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday)),0)

Confirm with control+shift+enter.
 

sue.Caradonna

New Member
Joined
Jan 17, 2005
Messages
8
It Works! Thanks so much (I didn't see the second formula in your first reply. That'll teach me to really look and read properly.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top