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​

 

[Indystick]

I don't understand why you're using COUNTIF on a single cell here, makes no sense. If you want to see if a value in a single cell is > than some number, why not just write it as IF(A$149 > 0) ? Similarly, your first two tests could be simplified to SUM(A$149,A150) = 2 for both cells have to be >0 for the SUM to equal 2. I suspect there's a lot of this type of simplification that could occur within the formula which would probably speed its calculation.

 

[Special-K99]

Why do you have COUNTIF around a single cell, you COUNTIF on a range not a single cell?

There's a shorter way than this too I'm sure

IF(AND(A149>0,A150>0),A149/A150-1,0)+
IF(AND(A149>0,A150<0),-(A149/A150-1),0)+
IF(AND(A149<0,A150>0),A149/A150-1,0)+
IF(AND(A149<0,A150<0),A150/A149-1,0)+
IF(AND(A149=0,A150>0),-1,0)+
IF(AND(A149=0,A150<0),1,0)+
IF(AND(A149>0,A150=0),1,0)+
IF(AND(A149<0,A150=0),-1,0)

 

[Special-K99]

What happens if A149=0 AND A150=0 ?

That situaion hasn't been catered for.

There are two factors A149 and A150.
Each of these can have three different outcomes, less than zero, equal zero, or greater than zero.

That's 9 (3 * 3) possible outcomes.

You've only accounted for 8 of them.

 

[Mathman]

It's highly improbable to have the values equal zero. I will give your suggestion a try and see how it works out.


Thanks!
MM ​

 

[Eric W]

Well, using COUNTIF on a single cell is usually overkill. You can change:

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

to

=IF(AND(A$149>0,A150>0),A$149/A150-1,0)

Next, your result is always some form of A$149/A150-1 (except for the <0, <0 part which is A150/A$149). Calculate that first, then worry about the sign. Something like:

=IFERROR(A$149/A150-1,1)*IF(SIGN(A$149)<>SIGN(A150),-1,1)

This formula does not match the results of your formula in all cases, so you'll need to tweak it, but I don't know what you're trying to calculate so I can't do much better. But it's much simpler, and you should be able to adapt it.

 

[Special-K99]

I was thinking of constructing a table of 9 elements thus

1. A150/A149-1
2. -1
3. A149/A150-1
4. 1
5. 0 (A149=0 and A150=0)
6. -1
7. -(A149/A150-1)
8. -1
9. A149/A150-1

These are the 9 outcomes ranging from <0, <0 to >0, >0

Then use

(SIGN(A149)+2)*(SIGN(A150)+2)
as an index to that table using INDEX() and do an evaluate on the result

 

[Indystick]

It's difficult to see what you're trying to accomplish here. What matters here in terms of logic? Is it:

• the pairwise combinations (e.g., 0,0)
• the sum of the values =2,
• both?

Also, are the values you're testing on limited to (-1, 0 , 1) or can they be any number?

Can you itemize the unique value_if_true and value_if_false arguments? I often find it easier to identify the possible outcomes and then identify the conditions that apply to them.

 

[Mathman]

Here are all the variations possible I am trying to capture. First number refers to cell A$149 and the second number refers to cell A150. Hope this helps!


1 2 1/2-1
2 1 2/1-1
1 -2 1/2-1*-1
-2 1 -2/1-1
-1 -2 -2/-1-1
-2 -1 -1/-2-1​

 

[Eric W]

After spending too much time on this, I think Special-K99's formula from post 3 is best. That removes the unneeded COUNTIFs, but leaves the formula easy to read, especially with the line feeds.

Using his idea from post 7, and constructing the table (D148:F156) I constructed a shorted formula with CHOOSE. However, without the table, the formula is a bit obscure.

	A	B	C	D	E	F
Based on post 1
sign of A149	sign of A150	Result
A150/A149-1
A149/A150-1
1-A149/A150
A149/A150-1

<tbody>
[TD="align: center"]146[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]147[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]148[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-80%[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]149[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]150[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-80%[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]151[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]152[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-80%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]153[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]154[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]155[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]156[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C148[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C150[/TH]
[TD="align: left"]=IF(AND(A149>0,A150>0),A149/A150-1,0)+
IF(AND(A149>0,A150<0),-(A149/A150-1),0)+
IF(AND(A149<0,A150>0),A149/A150-1,0)+
IF(AND(A149<0,A150<0),A150/A149-1,0)+
IF(AND(A149=0,A150>0),-1,0)+
IF(AND(A149=0,A150<0),1,0)+
IF(AND(A149>0,A150=0),1,0)+
IF(AND(A149<0,A150=0),-1,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C152[/TH]
[TD="align: left"]=CHOOSE((SIGN(A$149)+1)*3+(SIGN(A150)+2),A150/A149-1,-1,A149/A150-1,1,0,-1,1-A149/A150,1,A149/A150-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


All 3 formulas return the same results, as far as I've tested. It still kind of nags me that there should be a cleaner, shorter formula, but I fear that it would require a bit more analysis of your model.

Good luck.