Solved

# remove #NUM! error

Posted on 2013-11-14
567 Views
Hi, i have a formula

=PERCENTILE.EXC(IF(ISNUMBER(MATCH(InputRange,{1,3,4,5,6},0)),IF(InputRange<>"",CashFormula)),'S7 - Product Risk Scenario'!D28)

If there is no data there it creates a #NUM! error and makes the data that has returned difficult to read, can anyone add to the formula to return zero when no data is present in the ranges

Many thanks
Seamus.
0
Question by:Seamus2626

LVL 31

Accepted Solution

Rob Henson earned 250 total points
ID: 39647621
Wrap it all in an IFERROR statement.

=IFERROR(YourFormula,0)

Thanks
Rob H
0

LVL 48

Assisted Solution

Rgonzo1971 earned 250 total points
ID: 39647623
Hi
In XL2007 and further

=IFERROR(PERCENTILE.EXC(IF(ISNUMBER(MATCH(InputRange,{1,3,4,5,6},0)),IF(InputRange<>"",CashFormula)),'S7 - Product Risk Scenario'!D28),0)

Regards
0

Author Closing Comment

ID: 39647629
Perfect guys,

Many thanks
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.