IF statements ???

[wrenlea]

Hi
I need to get a cell to return a value depending on a number of things which i am trying ot use "if" for
The cell in question is C104 and so far I have the following:
=IF(E99="1",F73*D99,F73*D99/2)
Clearly this will be clear to you all.
HOWEVER I need this cell to return this value ONLY if cell F99 has a Y in it and if not C104 must be zero.
I hope this makes sense and hope even more that someone can help me please.
I think i need a further nested if ?? but have yet to fathom this

(Ideally the value for C104 should only be divided by 2 when the value of D99 is 2 or more ?? )

Thanks

Peter

 

[Aladin Akyurek]

=(E99=1)*(F99="Y")*(F73*D99/MAX(1,(D99>=2)+0))

 

[Egress1]

Howdy wrenlea,

=IF(AND(F99="Y",E99="1"),IF(D99>=2,F73*D99/2,F73*D99),0)

This ought to work (fingers crossed )

 

[Dave Patton]

you could use either of the following; I asumed test for number 1 and not text.

=IF(F99="Y",IF(E99=1,F73*D99,F73*D99/2),0)

or

=(F99="Y")*((E99=1)*0.5+0.5)*(F73*D99)

N.B. I did not notice the "(Ideally the value for C104 should only be divided by 2 when the value of D99 is 2 or more ?? ) "

 

[Egress1]

:D Yup Dave you're right of course it -should- be the number 1 .... I used the Text 1 since wrenlea used the Text.

=(F99="Y")*((E99=1)*0.5+0.5)*(F73*D99)

Can you explain this one for me? I understand the splats but what is the 0.5+0.5 how is that working?

I love this board I learn something new everyday.

And Aladin if you have the time can you explain your use of MAX in your formula?

TIA

 

[Aladin Akyurek]

=(E99=1)*(F99="Y")*(F73*D99/MAX(1,(D99>=2)+0))

This should be...

=(E99=1)*(F99="Y")*(F73*D99/MAX(1,2*(D99>=2)))

 

[Dave Patton]

=(F99="Y")*((E99=1)*0.5+0.5)*(F73*D99)

Can you explain this one for me? I understand the splats but what is the 0.5+0.5 how is that working?

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

I guess I didn't read the full question. You can adapt one of the approaches to consider the D99 >2


With Excel functions, a True is equivalent to 1 and False= 0.

(F99="Y) if True multiple the rest by 1; if False multiple by 0 (result 0)


(F73*D99) is * 1/2 but if E99=1 it is * 1/2+1/2 (1)

 

[Aladin Akyurek]

...And Aladin if you have the time can you explain your use of MAX in your formula?

The (corrected) MAX bit secures the denomitor to be either 1 or 2, the latter when D9 >= 2.

 

[Egress1]

The (corrected) MAX bit secures the denomitor to be either 1 or 2, the latter when D9 >= 2.

I get it.... very tidy thanks

 

[Egress1]

[quote="Dave Patton

(F99="Y) if True multiple the rest by 1; if False multiple by 0 (result 0)


(F73*D99) is * 1/2 but if E99=1 it is * 1/2+1/2 (1)[/quote]

Wow talk about a blonde moment! * .5 = /2 I dunno why that didn't click when I read it the first time.... too much partying in my youth is my excuse

Thanks