October 2019 - Profit Chart

Hi,

On paper 1 we are asked to produce a chart to illustrate the variability of potential outcomes. In the solutions, this is done by typing out all the possible ranks (1 to 100) and then using the MATCH function to look up the simulation number of that rank. However, my formulae returns #NA errors because there are some ranks missing (e.g. there is no rank 65 in my data, and this is consistent with the solutions too), whereas the examiners solution doesn't seem to have this error, despite them missing a rank 65 too. There is no mention of any corrections or amendments in the audit trail, and the solutions don't even have formulae in the profit chart tab after the top row, so I'm not sure how they've got the results out. Can someone help?

Thanks.

 

I think the examiners slipped up a bit here - that solution shouldn't really have been uploaded, given that a) it's an xlsm, and b) it has the column you're referring to copied/pasted as values.
However, to get around this, there's the RANK.EQ function - it works exactly the same, but if two results are equal, it won't assign the same rank - it'll give them two different ones (so you'll get your 65).

However, in this situation, you wouldn't get marked down for having a #NA in the table - because it doesn't affect the graph at all, you'd be fine.

 

Thank you!