Shortening If-Then equations

[spacely]

Hi,

I have excel 2016. I wonder if there's a shorter way to do If-Then, to not repeat an emvedded equation twice, in one cell.


If (big-long-thing=-10000, then "", big-long-thing)

Want to avoid double length equations in excel, if I can.

Thanks!

 

[snjpverma]

If you give a formula then we may try to shorten it. As of now it looks fine

 

[ParadoxuM]

Hi Spacy,

Long excel formulas are typical, and as long as thinking out logically then it can be as big as needed instead of shortening it. If you do feel your formula is overlapping or causing issues you are more than welcome to post it and see if we can sort you out!

 

[shg]

=iferror(1 / (1 / (biglongthing - 10000)), "")

 

[spacely]

Almost! And brilliant. Corrected:

=IFERROR((1 / (1 / (-10001 + 10000)))-10000, "")

Tested the -10001 varying any value, when it passes through -10000 it returns the blank, otherwise returns the value entered.

 

[spacely]

Well shoot. It looks to return the same number, but apparently it's off a little? Because an If-Then can't recognize the value anymore, although it looks the same ...

 

[spacely]

Is it because I'm using 10^10 instead of 10000?

 

[shg]

Oops:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

biglongthing​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

9999​

[/td][td="bgcolor:#E5E5E5"]

9999​

[/td][td]B2: =IFERROR(1 / (1 / (A2 - 10000)) + 10000, "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

10000​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

10001​

[/td][td="bgcolor:#E5E5E5"]

10001​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

548277​

[/td][td="bgcolor:#E5E5E5"]

548277​

[/td][td][/td][/tr]
[/table]

 

[spacely]

My "big-long-thing" is the max of a bunch of numbers with lots of decimal. I put that max equation in where you have your A2. Then I subtract the original max with what your logic returns, and get a small difference... -5.06645E-10

 

[shg]

Always better to ask the question you want answered ....