Ms Britches
simple formula
-
-
-
Welcome to the forum!
Can you be a bit more explicit in your question? Are you trying to set the font for the text? where? In an Excel header? -
did you get my second message?
-
2 cells – commissions for company and agents
B54 is empty – has to be input by user
B-51 holds formula needed
The sum of both should hold 100%If a number, 40%, is input into B54, B51 should reflect 60%, if B54 is null, B51 should reflect ‘0’
I just can’t seem to make the formula work.
=IF(b54>0, (100.00%-B54), b54,"0")
Sending it again, I don't see it in the thread and I'm new at this, so forgive me.
-
=IF(LEN(B54),100%-B54,0)
format b51 for percentages
-
-
LEN (length) is not what I'm after.
I need 'if B54 =40 then B51=60' if nothing is in B54 then B51 should reflect '0'
I think it has to be an "IF" function, but don't know how to set it up. Thanks for your help, and quick response. -
Enter into B51:
=if( isblank( b54), 0, 1 - b54)
This formula looks at B54 to see if there is something there (no error-checking for text vs. number--see below). If there is 'nothing' in B54, then '0' is returned (the IF is true).
If there is 'something' in B54 (the IF is false), then enter the value "1 - B54" (or 100% minus whatever % value is in B54).
Here's additional error-checking:
=IF( ISBLANK( B54), 0, IF( NOT( ISNUMBER(B54)), "Enter a NUMBER in B54!", 1 - B54))Chris
-
You also might want to add Data Validation to B54 to make sure any entry is between 0 and 1 (0 and 100%).
-
"LEN (length) is not what I'm after. "
Did you test the formula? The len() check is to determine whether there's anything in the cell. If not, the len() check evaluates to zero, which is interpreted as false in the if() formula, hence the formula returns zero. Otherwise, the true portion is evaluate, this being the calculation you requested.
-
Neat. I keep learning from you, Paddyd. I had thought you'd just forgotten a "<1" in your LEN function. Now I know that a zero serves the same purpose as FALSE.
The only issue with your solution is that it needs error checking: e.g., a space in B54 would generate an answer of 1 instead of the desired 0 if B54 is "blank" -
-
"The only issue with your solution is that it needs error checking: e.g., a space in B54 would generate an answer of 1 instead of the desired 0 if B54 is "blank" "
Your previous suggestion re data validation is a good one My formula would also return an error for text entries in the cell. I guess Blue_Hornet's formula is the most comprehensive in this regard.
-
Quote
Originally posted by Paddyd
=IF(LEN(B54),100%-B54,0)format b51 for percentages
Thanks, you're a life saver. I've slept since I replied yesterday, came in this morning, plugged formula in and it works! Thank you again for your help.
Gail
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!