Solved Subtract 100% from value in a specific cell as IF condit

June 21, 2016 at 05:38:04
Specs: Windows 64
Thank you for all the help everyone in this forum has provided thus far! I truly appreciate it!

I have this formula which is not working the way I need it to...

=IF(ISBLANK(C5),"",IF(C5>=1,100-F5,0))

This part specifically 100-F5

The 100 is suppose to represent 100% and the value in F5 is a %. I keep getting 9930%. The value in F is 70%.

TY!


See More: Subtract 100% from value in a specific cell as IF condit

Report •


#1
June 21, 2016 at 06:05:07
I think this may work

=IF(ISBLANK(C5),"",IF(C5>=1,100-F5*100,0))

this should return 30 if I am correct?

message edited by AlwaysWillingToLearn


Report •

#2
June 21, 2016 at 06:20:02
✔ Best Answer
If the value in F5 is formatted to display 70% and the cell with the formula is formatted the same way, I believe that this will get you what you want:

=IF(ISBLANK(C5),"",IF(C5>=1,100%-F5,0))

This should return a Blank cell or 30% or 0%

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

message edited by DerbyDad03


Report •

#3
June 21, 2016 at 09:06:40
Thank you for your help. I actually place () around 100 without the % and it worked like a charm!

Report •

Related Solutions

#4
June 21, 2016 at 10:13:03
FWIW, that makes absolutely no sense.

With 70% in F5, and the cell containing the formula formatted as a percentage, both of these formulas return 9930%

=IF(ISBLANK(C5),"",IF(C5>=1,100-F5,0))

=IF(ISBLANK(C5),"",IF(C5>=1,(100)-F5,0))

This formula returns 30%

=IF(ISBLANK(C5),"",IF(C5>=1,100%-F5,0))

I can't think of any scenario where placing () around a single hardcoded number will change the result of the formula. Parentheses control the order of operation of the formula. Placing parentheses around a single hardcoded number does nothing to change the order of operation.

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

message edited by DerbyDad03


Report •

#5
June 21, 2016 at 14:53:21
You are correct! I compared the formula I pasted into my initial question earlier today vs. the formula I ended up with after I continued to play with the formula while I waited for an answer and again, you are correct, if I () 100 it did not work.

Thank you again!


Report •


Ask Question