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

Your instruction make no sense. First you say:

"If" cell Q1 is less than $0.00 then proceed with the calculation Q1/(E1+I1)-1Which 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

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

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

Franci: Just FYI...

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

I check for Q1

less than0 and proceed from there while Mike checks for Q1greater than0. The results will be the same except for when Q1equals0.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.

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

Ask Your Question

Weekly Poll

Do you think its a good idea for Amazon to divide its new second headquarters between two locations?

Discuss in The Lounge

Poll History