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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
Or even better:

=IF(ISERROR(MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday))),0,MEDIAN(IF((Team=$S$3)*(JobClassification=$T$3),AgeToday)))
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
Also...

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

Confirm with control+shift+enter.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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
Back
Top