Is their a simply faster Variation of this formula?

[Mathman]

Hi


Any idea as to how this can re-written so it can be simpler and calculate faster?


=IF(COUNTIF(A$149,">0")+COUNTIF(A150,">0")=2,A$149/A150-1,0)+IF(COUNTIF(A$149,">0")+COUNTIF(A150,"<0")=2,(A$149/A150-1)*-1,0)+IF(COUNTIF(A$149,"<0")+COUNTIF(A150,">0")=2,A$149/A150-1,0)+IF(COUNTIF(A$149,"<0")+COUNTIF(A150,"<0")=2,A150/A$149-1,0)+IF(COUNTIF(A$149,"=0")+COUNTIF(A150,">0")=2,-100%,0)+IF(COUNTIF(A$149,"=0")+COUNTIF(A150,"<0")=2,100%,0)+IF(COUNTIF(A$149,">0")+COUNTIF(A150,"=0")=2,100%,0)+IF(COUNTIF(A$149,"<0")+COUNTIF(A150,"=0")=2,-100%,0)


Thanks!
MM​

 

[Mathman]

That really is appreciated what a great help.


I tries it out and at first glance works well but only 2 look off to me


1 -2 result gives 150% should it not be 300%?
-2 -1 result gives -50% should it not be -100%?




Been wracking my brain round this a little too much also today so if I'm completely off do let me know and if a change can suggested that would be great!


MM​

 

[Eric W]

This is what I meant by further analysis of your model. Both of your examples give the exact same results from all 3 formulas, which is what I'd expect since Special-K99's and mine just use the formulas you provided. We both just rewrote your formula for clarity and brevity. But we could not rewrite it for accuracy since we don't know what you're trying to calculate.

Some of your formulas look "off" to me, not consistent with the others. Clearly you're trying to create a percentage, but of what? What does the value in A149 represent? What does the value in A150 represent? Under what circumstances will they be positive? negative? zero? And what does the percentage (in words) represent? Growth? Capacity? Progress to a goal?

 

[Mathman]

I am trying to get the percentage of companies revenue. On somewhat rare occurrences a company will report negative earnings, my goal is to try and represent those results as best as possible.


By far the choose formula is the closest and fastest of the 3. 12X faster than the Countif and 3X faster then the If/AND. and uses a much higher amount of multi-threading.


Any idea how it can be a little tweaked to reflect these changes?

1 -2 result gives 150% should be 300%
-2 -1 result gives -50% should be -100%​

Thanks again for the great help!

MM​

 

[Eric W]

Are you saying that A149 is last year's earnings, and A150 is this year's earnings, and you're trying to find the percent increase (decrease)?

 

[Mathman]

Are you saying that A149 is last year's earnings, and A150 is this year's earnings, and you're trying to find the percent increase (decrease)?

A149 is this years earnings and A150 is last years earnings. And you are correct, I am trying to get the percentage increase or decrease.


MM​

 

[Eric W]

OK, given that, we just need to consider 3 cases, where last year's earnings are positive, zero, and negative.

If last year's earnings were positive, your A149/A150-1 formula works fine to calculate the percentage increase/decrease.

If last year's earnings were 0, then you can't calculate any kind of reasonable percentage increase. If you start with 1 dollar, and double it to 2 dollars, you can say you're 2 times better (100% better). But if you go from 0 to 1, how many times better are you? 1? 10? 1,000,000? It's equivalent to dividing by zero, which is a no-no.

Now if last year's earnings were negative, you still face a similar problem, -1 to 1 means you're -1 times better? Nonsensical. However, if you want to say that OK, I lost 1 dollar last year, but I made twice as much as I lost year this year, so 200% improvement (not strictly mathematically correct, but I suppose you can get something out of it), then you can use the same formula, but you'll need to multiply the result by -1.

Given that, you can combine those 3 cases into one formula:

=IF(A150=0,"No percentage comparison possible",(A149/A150-1)*SIGN(A150))


Hope this makes sense!