IF Function Question

koeio · External Since: Jul 19, 2012 Posts: 1

Hi

I Would like to know if it is possible to do something like this using
IF:

From this:
=IF(A1*A2>100;A1*A2;0)

To something like this:
=IF(A1*A2>100;ResultFromTest;0)

Where "ResultFromTest" variable will be the result from the test
"A1*A2", this way removing code redundancy.

Imagine how much a formula like this would be simplified:
From:
=IF(100-((A1*A2+(A3/A4))*A5/100)>45;100-((A1*A2+(A3/A4))*A5/100);0)
To:
=IF(100-((A1*A2+(A3/A4))*A5/100)>45;testResult;0)

Thanks

--
koeio

Claus Busch · External Since: Oct 27, 2009 Posts: 113

Hi,

Am Thu, 19 Jul 2012 16:30:21 +0000 schrieb koeio:

> I Would like to know if it is possible to do something like this using
> IF:
>
> From this:
> =IF(A1*A2>100;A1*A2;0)
>
> To something like this:
> =IF(A1*A2>100;ResultFromTest;0)
>
> Where "ResultFromTest" variable will be the result from the test
> "A1*A2", this way removing code redundancy.

e.g. enter =A1*A2 in G1, select Name manager and give G1 the name
ResultFromTest. Then you can use the name in the formula. But you don't
have to do it with an If-Statement. Try:
=(A1*A2>100)*ResultFromTest
or
=(ResultFromTest>100)*ResultFromTest

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

koeio · External Since: Jul 20, 2012 Posts: 1

Thanks for your response

Your solution is not exactly what I want because you need to use other
cells to implement.

Imagine you want to use tables with formulas like this:

Code:
--------------------

Formula X Y
40 5 8
0 2 3
63 7 9
0 1 8
80 8 10

--------------------

Where the cell Formula has this: "=IF([X]*[Y]>30;[X]*[Y];0)"

Is there anyway to do this "=IF([X]*[Y]>30;ResultFromTheLogicalTest;0)"
without creating another column only for the "[X]*[Y]" calculation or
use other cells?

Maybe some hidden IF parameter that does this:
"=IF([X]*[Y]>30;@result;0)" or other kind of function that I don't
know.

Thanks

--
koeio