# Solved Need help writing simple Excel formula

Discussion in 'Business Applications' started by Harry32, Feb 9, 2016.

Not open for further replies.

Joined:
Jul 28, 2009
Messages:
124
I'm trying to write a formula for a spreadsheet in Excel 2010, but I'm obviously not doing it correctly because Excel just recognizes the formula as text. What I want is a formula in cell E6 that produces a value of 30 times the value of Cell C6, but only if the value of Cell D6 ≥ 0.

What I entered as a formula in Cell E6 is: IF(D6≥C6),E6=30*C6

I suspect it needs more or fewer parens or commas or something. Can anyone straighten me out? Many thanks.

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
you need to add an = infront in order for excel to recognise as a formula or calculation

=IF(D6≥C6), E6=30*C6 ,false) - although the formula is incorrect
Also you need to add the false section - what do you want if false ?

=IF ( Test , True , False )
Evaluates the Test and if the result is true - then it carries out whatever you put in true , if the test is false it carries out the false part

Put this into cell E6
=IF( D6 >= 0 , 30*C6 , false )

The test is
Is D6 greater than or equal to zero ( Note the = follows the greater than sign - must be in that order to work >=
If that is true and D6 is equal to or greater than a zero - it will do the true part - 30*c6
But blank spaces are also considered zero - so if D6 has no content that will also be considered true
If D6 is less than zero - the result will be false and in my example - False will appear in the cell
if you want a blank cell instead then
=IF( D6 >= 0 , 30*C6 , "" )
otherwise , as mentioned above - what do you want if D6 is less than zero

Joined:
Jul 28, 2009
Messages:
124
Thank you, Wayne. I've worked extensively with Excel ever since it was created (about 25 years ago?) and use it constantly at present, but I've never had occasion to write a conditional formula until now. I'm uncertain about the need for embedding the words TRUE and FALSE in a conditional formula and other details of the formula structure. If I could see a formula that performs the following and demonstrate it on my spreadsheet, I might understand the process better and learn to write other similar formulas.

I want to place a formula in Cell E6 that produces a value of 30 times the value of Cell C6, but only if the value of Cell D6 is ≥ 0. If Cell D6 is < 0, Cell E6 should remain empty.

What would such a formula look like? Thank you –

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne
As i posted
=IF( D6 >= 0 , 30*C6 , "" )

=IF ( Test you want to carry out , The formula or value you want if the test result is True , The formula or value you want if the test result is False )

So the Test in your case is
Is the cell D6 Greater than or equal to zero
D6 > = 0
If that test is TRUE then do the True section of the formula
30 * C6
If that test is TRUE then do the True section of the formula
for a blank we use ""

D6 >= 0
Will also be true if the cell D6 is blank - as Excel sees blanks as zero in formulas

So we could avoid that by adding another condition to the Test
in this case an AND

AND( D6 >=0 , D6<>"")

=IF( AND( D6 >=0 , D6<>"") , 30*C6 , "" )

hope that helps

Joined:
Jul 28, 2009
Messages:
124
Wayne, with your help I've managed to create a semi-automatic score sheet for bridge (copy attached). It's not intended to replace the typical manual score sheet that everyone uses for play, but I seized on the idea as a way to start learning how to write conditional formulas for Excel worksheets. I'm pleased with the result despite its limitations.

I still have an awful lot to learn with respect to conditional Excel formulas. Where can I find a list of commonly used symbols and abbreviations for conditional formulas, e.g., >=, <>, "", etc. Is the same as >=? What introductory words beside IF and AND are commonly used? Words like UNLESS or UNTIL? Where can I find sequencing rules for formulas having multiple conditions?

Thanks very much.

File size:
17.1 KB
Views:
1
6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,910
First Name:
Wayne

There are quite a few websites which will provide tutorials and detail on excel functions - i dont use any particular site
This is quite a good site
http://www.cpearson.com/Excel/Topic.aspx

If you just use the functions in excel you will see how they work - and clicking on the Fx next to the formula bar will also bring up the functions and there is help for each of those

Some of the logical functions
AND() OR() NOT() XOR()

Yes

> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
"" set the cell blank
<> not equal to

Joined:
Jul 28, 2009
Messages:
124
Thanks for the additional information. I think I can take it from here, but if I run into some snags, I may get back to you.

As Seen On