Is their a simply faster Variation of this formula?

Mathman

Board Regular
Joined
Jan 28, 2017
Messages
152
Office Version
  1. 2016
Platform
  1. Windows


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
 
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
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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?
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top