Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement help
I am trying to forecast revenue using an if statement. I'm trying to take a
date and result with a number from another cell. I have a projected revenue date in B13 formatted as mmm-yy (Jun-08) A revenue number is located in C13 (1000) I write this formula =IF(B13="Jun-08",C13,0) My result is always 0 and I want 1000. Any suggestions? BTW - I'm not a pro in excel so I might need a thorough explaination. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement help
Try this:
=IF(TEXT(B13,"mmm-yy")="Jun-08",C13,0) -- Biff Microsoft Excel MVP "Jena" wrote in message ... I am trying to forecast revenue using an if statement. I'm trying to take a date and result with a number from another cell. I have a projected revenue date in B13 formatted as mmm-yy (Jun-08) A revenue number is located in C13 (1000) I write this formula =IF(B13="Jun-08",C13,0) My result is always 0 and I want 1000. Any suggestions? BTW - I'm not a pro in excel so I might need a thorough explaination. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement help
Try this;
=IF(B13=DATEVALUE("Jun-08"),C13,0) You have two probable issues; 1. Data type mismatch. Your formula compares a text string ("Jun-08") to a date. Dates are actually stored as numbers (8-Jun-2008) = 39607, so effectively you're saying "if that basket holds 10 apples, then do this" but the basket only ever holds pears. The DateValue function included in my example effectively converts pears to apples (text to a date number) in order to make a valid comparison. 2. It isn't clear which date in June you mean - you probably mean the entire month. Even though you have specified the format in B13 to show only mmm-yy, it must, if it is truly date formatted, have a day. If you have not specified a day, it has probably defaulted to 1/6/08, which should work with the formula above. It works for me. If you did specify a day, and it was not the first, set it to the first. It should work then. There are more complicated methods which pull out only the name of the month and then use it for comparison, but this is the simplest thing I can think of that meets what I can see of your requirements. Most importantly, play with it until you understand it or it will be easy to make a similar error. Do some experiments. Here is a copy of the Help file's contents for DateValue; DATEVALUE See Also Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number. Syntax DATEVALUE(date_text) Date_text is text that represents a date in a Microsoft Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates. Using the default date system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Using the default date system in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range. If the year portion of date_text is omitted, DATEVALUE uses the current year from your computer's built-in clock. Time information in date_text is ignored. Remarks Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for the Macintosh uses a different date system as its default. Most functions automatically convert date values to serial numbers. Example The example may be easier to understand if you copy it to a blank worksheet. How? Create a blank workbook or worksheet. Select the example in the Help topic. Do not select the row or column headers. Selecting an example from Help Press CTRL+C. In the worksheet, select cell A1, and press CTRL+V. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. 1 2 3 4 5 A B Formula Description (Result) =DATEVALUE("8/22/2008") Serial number of the text date, using the 1900 date system (39682) =DATEVALUE("22-AUG-2008") Serial number of the text date, using the 1900 date system (39682) =DATEVALUE("2008/02/23") Serial number of the text date, using the 1900 date system (39501) =DATEVALUE("5-JUL") Serial number of the text date, using the 1900 date system, and assuming the computer's built-in clock is set to 2008 (39634) Note To view the number as a date, select the cell and click Cells on the Format menu. Click the Number tab, and then click Date in the Category box. On May 5, 2:12*pm, Jena wrote: I am trying to forecast revenue using an if statement. I'm trying to take a date and result with a number from another cell. I have a projected revenue date in B13 formatted as mmm-yy (Jun-08) A revenue number is located in C13 (1000) I write this formula =IF(B13="Jun-08",C13,0) * My result is always 0 and I want 1000. Any suggestions? *BTW - I'm not a pro in excel so I might need a thorough explaination. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement help
Jena, this was posted while I was writing.
It makes the reverse conversion (Date to Text) that I suggested and forces it to your format. Choosing which to use boils down to taste, though the format forcing here looks useful. If your spreadsheet is large enough, one or the other may be faster, but I don't know the details there and it's probably beyond your requirements. On May 5, 2:48*pm, "T. Valko" wrote: Try this: =IF(TEXT(B13,"mmm-yy")="Jun-08",C13,0) -- Biff Microsoft Excel MVP "Jena" wrote in message ... I am trying to forecast revenue using an if statement. I'm trying to take a date and result with a number from another cell. I have a projected revenue date in B13 formatted as mmm-yy (Jun-08) A revenue number is located in C13 (1000) I write this formula =IF(B13="Jun-08",C13,0) My result is always 0 and I want 1000. Any suggestions? *BTW - I'm not a pro in excel so I might need a thorough explaination.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement help
This works perfect for my needs. Thank you so much for the quick response.
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Jena" wrote in message ... This works perfect for my needs. Thank you so much for the quick response. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
IF and AND statement | Excel Worksheet Functions | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |