?
Solved

Array formula result will not display

Posted on 2013-11-13
7
Medium Priority
?
461 Views
Last Modified: 2013-11-14
Hi.

 i hae this formula

=PERCENTILE.EXC(IF(OR(Testing=1,Testing=3,Testing=4,Testing=5,Testing=6),CashFormula),'S7 - Product Risk Scenario'!D28)

The result in the formula bar is 4,511,758,955

in the cell, 0 appears.

There is also the error message "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated"

But if i was to put in a vlookup, it would return a result.

Can someone share some thoughts?
#
Thanks
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 39645851
Did you press Shift-ctrl-enter?

If not then
select the formula cell
press F2
press Shift-ctrl-enter
0
 

Author Comment

by:Seamus2626
ID: 39645862
I have ssaqibh,

Im not sure why its displaying as zero
0
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 1000 total points
ID: 39645866
What's the format of the cell?

What do you mean by: "The result in the formula bar is 4,511,758,955".  Where exactly do you mean?  Is that the expected result being displayed?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Seamus2626
ID: 39645882
If i go up to the formula bar and click Fx, the Function Arguments show me the results, and the result is valid and returns "4,511,758,955"

Thanks
0
 
LVL 23

Accepted Solution

by:
NBVC earned 1000 total points
ID: 39645909
I guess the error message is "stopping" the calculation from displaying in the cell.

I just used your formula in a workbook I created and filled over 1000 rows in the CashFormula range, and had no issues.  I am using Excel 2013.  I also copied down a bit and had no errors.   So I am not sure how big your ranges are, how many formulas you have, and what other formulas are in your workbook, and if maybe they are volatile or not....
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 39645980
OR function doesn't normally work as required in these sort of formulas

Assuming that Testing and CashFormula are ranges I don't think your formula will do what you want - OR only returns a single result (TRUE/FALSE) not an array of TRUE/FALSE values, so you'll either get all of CashFormula....or none of it, not specific rows as I think you want. Try this version

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

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 

Author Closing Comment

by:Seamus2626
ID: 39647417
Hey guys, it was a case of saving down my excel ss somewhere else, one of those excel things, all good now

thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question