Is there a way to simplify this calculation?

Well, you can lose a lot of the brackets for a start:

Formula:

Please Login or Register  to view this content.

But, it's an old style error check using ISERROR and repeating the formula.

If you are using Excel 2007 or above, you can use IFERROR instead. The error value here is 5%.

So, it becomes:

Formula:

Please Login or Register  to view this content.

Which is a little shorter and clearer.


Regards, TMS

That said, it actually looks as though it should be an Array Formula for a bigger range than just H22.

You can look at the component parts:

HTML Code: 

HTML Code: 

Note that the literal "N/A" is not the same as the error code generated by a formula error (or by using =NA())

HTML Code: 

Regards, TMS

Forget the second post. I was just working through each of the components to see what values they, individually, returned. No matter.

I don't know what the sheet/data looks like so I cannot offer an informed opinion but it feels as though much of the formula (even the new short version) is redundant.

However ...


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Shame you can't mock up a sample. But, I'm sure it will be better than it was.

Thanks for the rep.

Surely the formula could just be something like:

Formula:

Please Login or Register  to view this content.

Regards, TMS

I've put this code into the empty Module4:

Please Login or Register  to view this content.

I've also shortened the code in Module 6 to this:

Please Login or Register  to view this content.

I have set up a Static Named Range containing the percentages on the Formulae sheet. This allows me to use this formula in the majority of the cells:

H7:

Formula:

Please Login or Register  to view this content.

but note that one formula is different, hence the code above.

Separating the percentages out means you can more easily adjust the percentages applied to each of the questions ... and the formulae are consistent.

Anyway, see the updated sample. Unless I am much mistaken, the alternative formulae produce the same results as the shortened replacements for the original formulae. It's easy to swap between the two using the subroutines listed above.

Regards, TMS