If formula (Excel 2000)

Topic

New Reply

What should the formula return if G12 is greater than or equal to 3? The following will return 19.95 if G12 is less than 3 and null if G12 is Greater Than or Equal to 3.

=IF(G12<3,19.95,"")

Reply | Quote

Replies

(Edited by S Peters on 14-Jan-04 10:01. more info)

I’m not very familiar with Excel, but am trying to learn. I am trying to find a formula where if say G12 is less than 3, it should show 19.95. g12 is a sum of another 2 cells. I am attaching an example of what I am doing. The message I got from Legare Coleman keeps giving me an error, so maybe he needed to see what I was doing. I have tried using Excels formulas, but was unable to figure out just what they needed.

Reply | Quote

Stan,
There’s many, many formulas available in Excel. Excel has a nifty way to help you in choosing the best one, and to help out if you know a formula, but you’re not sure of the syntax.

To start, in a given cell you’d line a formula, just type an = equals sign. Now look to the left of the formula bar (where you can edit your cells), you’ll see the name of a function (it might look greyed out but it isn’t). That’s a drop down box that will give you access to any function in Excel. Choose a function there, and the formula palette will appear, which gives you “live” results and helps you fill in the correct syntax. If you type =if then click on that bold = equals sign, the formula palette for working out the IF function will appear.

Reply | Quote

Stan,
There’s many, many formulas available in Excel. Excel has a nifty way to help you in choosing the best one, and to help out if you know a formula, but you’re not sure of the syntax.

To start, in a given cell you’d line a formula, just type an = equals sign. Now look to the left of the formula bar (where you can edit your cells), you’ll see the name of a function (it might look greyed out but it isn’t). That’s a drop down box that will give you access to any function in Excel. Choose a function there, and the formula palette will appear, which gives you “live” results and helps you fill in the correct syntax. If you type =if then click on that bold = equals sign, the formula palette for working out the IF function will appear.

Reply | Quote

If someone has already replied, and you have further questions and/or additional information, it is better to post a reply. Editing the original post may confuse Loungers who read this thread later on, and you run the risk that it escapes the attention of whoever replied, because your reaction is higher up in the thread than the reply you react to.

Reply | Quote

You hadn’t told us that you are dealing with time values. Try this formula:

=IF(G12<TIME(3,0,0),19.95,"")

The Time function takes a number of hours, minutes and seconds and returns the corresponding time value. You will probably have to clear the time formatting Excel automatically applies to the result of the above formula (Edit | Clear | Formats)

Reply | Quote

The formula you gave me keeps giving me an error message. I was unable to clear format of results, because I couldn’t get results because of the error message. The blue cell I was wanting to display currency ( H column)

Reply | Quote

Does the attached modified version of your workbook work?

Reply | Quote

That is exactly what I want. Now I guess I need to formulate the rest of the cells in H to show nothing if they are zero or above 3. Thank you for your help.

Reply | Quote

That is exactly what I want. Now I guess I need to formulate the rest of the cells in H to show nothing if they are zero or above 3. Thank you for your help.

Reply | Quote

Does the attached modified version of your workbook work?

Reply | Quote

The formula you gave me keeps giving me an error message. I was unable to clear format of results, because I couldn’t get results because of the error message. The blue cell I was wanting to display currency ( H column)

Reply | Quote

You hadn’t told us that you are dealing with time values. Try this formula:

=IF(G12<TIME(3,0,0),19.95,"")

The Time function takes a number of hours, minutes and seconds and returns the corresponding time value. You will probably have to clear the time formatting Excel automatically applies to the result of the above formula (Edit | Clear | Formats)

Reply | Quote

Sorry, I missed that you edited the original message rather than replying to my message. You didn’t say that you were dealing with time values which introduces a different wrinkle. I am still not clear what you want to display in the blue cell. That cell is formatted as time, so if the formula returns 19.95 it will display as 22:48 (.95 of a day). Hans has given you a formula that will work with a time value in G12 and will return 19.95 if G12 is less than 3 hours and null if it is greater than or equal to. However, this will display as 22:48. If you want it to display as 19.95, you will also have to change the cell format of column H.

Reply | Quote

Sorry, I missed that you edited the original message rather than replying to my message. You didn’t say that you were dealing with time values which introduces a different wrinkle. I am still not clear what you want to display in the blue cell. That cell is formatted as time, so if the formula returns 19.95 it will display as 22:48 (.95 of a day). Hans has given you a formula that will work with a time value in G12 and will return 19.95 if G12 is less than 3 hours and null if it is greater than or equal to. However, this will display as 22:48. If you want it to display as 19.95, you will also have to change the cell format of column H.

Reply | Quote

(Edited by S Peters on 14-Jan-04 10:01. more info)

I’m not very familiar with Excel, but am trying to learn. I am trying to find a formula where if say G12 is less than 3, it should show 19.95. g12 is a sum of another 2 cells. I am attaching an example of what I am doing. The message I got from Legare Coleman keeps giving me an error, so maybe he needed to see what I was doing. I have tried using Excels formulas, but was unable to figure out just what they needed.

Reply | Quote