Formula Size Reduction / Simplification

[cossie2k]

Hi All,

I hope this finds everyone well.....

I have a formula......

=IF(AND((($K6-((VLOOKUP($N6,Register!$A:$AN,31,FALSE)/60)))/$K6)>=-$B$1,(($K6-((VLOOKUP($N6,Register!$A:$AN,31,FALSE)/60)))/$K6)<=$B$1),"Yes","No")

Its comparing 2 values and then stating "Yes" or "No" if it is, or is not, within a specified range, ie +/- 20%

K6 refers to value 1
the Vlookup refers to value 2
and $B$1 refers to the tollerance %

I currently have about 20,000 of these formulae in my spreadsheet, and its obviously slowed the spreadsheet right down.

Is there a way to incorporate a ISERROR statement to this? or a way to stop it calculating the field if the vlookup value is not yet present?

Any other size reducing suggestions appreciated!

Thanks for your help!

Alex

[OnErrorGoto0]

Hi,
May I suggest that you alter your thread title to something meaningful before a moderator comes along and raps your knuckles?

[cossie2k]

Its a fair point! But I cant get edit that bit! I think Ill be getting told off! Sorry mods, please could you assist!

[OnErrorGoto0]

Click Edit, then Go Advanced, then you can edit your title.

[cossie2k]

I suspect this is the shortist the formula can be but wanted a wider opinion!

[OnErrorGoto0]

I would strongly suggest putting this part in a separate cell:
=($K6-VLOOKUP($N6,Register!$A:$AN,31,FALSE)/60)/$K6
and then referring to that result in your formula rather than calculating it twice. I would also prefer:
($K6-INDEX(Register!$AE:$AE,MATCH($N6,Register!$A:$A,0))/60)/$K6

[Pepe Le Mokko]

Perhaps

Please Login or Register  to view this content.

[cossie2k]

Both suggestions appreciated guys,

Ive tried both suggestions and both work. Originally I did have the formula split out you suggest OnError. Am I right in assuming then that 2 small formulae will calculate than 1 large formula?

I think I will give Pepe's version a whirl for the time being. Is it just turning a minus value into a positive value?

Thanks for all your help,
Alex

[OnErrorGoto0]

It would be more efficient because you only calculate the VLOOKUP (or INDEX/MATCH) once, but Pepe's version takes care of that anyway.

[cossie2k]

That makes sense as its having to go into the other sheet and perform the Vlookup twice per formula. Definitely a point worth considering in the future as I currently have 5 columns of this formula over nearly 4000 rows and its likely to get bigger.

Thanks for all your help,

Kind Regards
Alex

[OnErrorGoto0]

If you are looking up the same column(N) for each of those columns, then I would definitely put a MATCH formula in a separate column and then refer to that directly in 5 INDEX formulas. It would be much more efficient.