Solved entering an IF formula

November 7, 2013 at 23:24:30
Specs: Windows 7
I need assistance to enter a formula -
"If" cell Q1 is less than $0.00 then proceed with the calculation Q1/(E1+I1)-1 and
the result / answer (formatted as a %) should be 0.0%.
the respective amounts are :-
Q1 = $0.00
E1 = $10.00
I1 = $ 9.00
if Q1 was $21.00 the answer is 10.5% .
Any assistance as to how to write the formula is greatly appreciated


See More: entering an IF formula

Report •


#1
November 8, 2013 at 05:36:32
Your instruction make no sense.

First you say:

"If" cell Q1 is less than $0.00 then proceed with the calculation Q1/(E1+I1)-1

Which means to me, that the contents of cell Q1 must be LESS than 0 for the calculations to occur.

then you say:

if Q1 was $21.00 the answer is 10.5%

How can Q1 be $21.00 and still produce your result, when you just said that the only time to calculate is if Q1 is less than Zero.

See my confusion?

MIKE

http://www.skeptic.com/


Report •

#2
November 8, 2013 at 05:39:21
Your requirements don't really make sense:

You said: "If cell Q1 is less than $0.00 then proceed with the calculation Q1/(E1+I1)-1 and the result / answer (formatted as a %) should be 0.0%."

If we proceed with the calculation Q1/(E1+I1)-1, the result will not be 0%.

Do you mean "If cell Q1 is less than $0.00 the result / answer (formatted as a %) should be 0.0%, otherwise proceed with the calculation Q1/(E1+I1)-1"?

If that's the case, then this should work for you:

=IF(Q1<0,0,Q1/(E1+I1)-1)

However, with that formula any value of Q1 that is less than the sum of E1+I1 will return a negative percentage. Is that OK?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
November 8, 2013 at 05:41:17
✔ Best Answer
I've re-read your post several times,
The wording of your post had me confused, I think this is what your looking for.

=IF(Q1>0,Q1/(E1+I1)-1,0)

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 8, 2013 at 05:51:40
Franci:

Just FYI...

The 2 formulas that Mike and I suggested are similar, but they will produce different results.

I check for Q1 less than 0 and proceed from there while Mike checks for Q1 greater than 0. The results will be the same except for when Q1 equals 0.

With my formula, the result for Q1 = 0 will be -100%, with Mike's it will be 0%.

However, in both cases, Q1 < E1+I1 will return a negative percentage.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
November 9, 2013 at 12:01:33
thanks for your replies -
Mike,s was the result I was looking for.
Basically I was looking for a % result that if cell Q1 was zero then the result should be zero whereas if Q1 had a positive value then the result would be a % increase or decrease
Franci

Report •

Ask Question