Ask Experts Questions for FREE Help !
Ask
    AJC_50's Avatar
    AJC_50 Posts: 3, Reputation: 1
    New Member
    		  
    #1

    Oct 4, 2007, 01:57 PM
    Excel formula IF function
    Hi.
    I am creaing a spreadsheet for work. Basically I have a column that shows all the project names, it shows a date and a price for the project.
    I need this price to then be automatically placed into the appropriate monthly column.
    I.e if the project is 20,000 and happened on the 21st of jan 2007 then the 20000 gets put into the jan 2007 column.
    I have figured out how to get the formula to work. But I have one small problem with it. If the month is does not match the January column then it displays as 0 which is correct but if the year is different then it says False instead. I need it so that instead of false it also says 0.

    The formula I have is
    =IF(YEAR(E17)=2007,IF(MONTH(E17)=1,SUM(D17),0))

    so if the project is in January 2007 then the price which is in cell D17 will be displayed.

    Many Thanks
    ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
    		  
    #2

    Oct 4, 2007, 02:21 PM
    Change the last part of the formula to look like this:

    ... (D17),0),0)

    Note that I added ",0" just in front of the last paren. This way if the cell E17 is not 2007, it will show a 0. Since you hadn't specified what to do in case E17 is not 2007, the spreadsheet showed "FALSE"
    KISS's Avatar
    KISS Posts: 12,510, Reputation: 839
    Uber Member
    		  
    #3

    Oct 4, 2007, 02:34 PM
    It's going to do that.

    IF(logical_test,value_if_true,value_if_false)


    Your test is OK, but the value_if_true is another test

    If you typed =If(Month(E17)=1) in a cell, it will return TRUE or FALSE hence this is what is being displayed.

    Write a second IF statement for argument #2 that has 3 arguments.
    The test, the value if the test is true and the value when the test is false.

    There is more here than meets the words and I'm missing it.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Display formula in Excel [ 4 Answers ]

Hi, Can you please show me how to display all formulas in a spreadsheet? I already calculated the data using formulas in a spreadsheet, now I just want display the formulas. Are there any keyword shortcuts. Many thanks.

Excel count function [ 12 Answers ]

I am trying to count a large group of cells that are colored depending on the data. For example: I want to count the number of red cells which are faults. I tried the countif but not sure how to set the criteria. Thanks for any help.

Excel formula [ 3 Answers ]

I have a column that you manually enter a number; either 0 (zero) or any number greater than zero. I have another column with the formula of: =DAYS360(AN22,AQ22) If the resulting formula figure/number is greater than the manually entered number then I want a third column to equal: $0.00 But...

Excel Count Function [ 4 Answers ]

In November 2004 Leif 5233 wrote the following regarding counting cells highlighted in a certain colour: ------------------ Create this function: Function CountCol(SumRange As Range, intColor As Integer) As Integer Dim I As Integer Dim Cell As Range Set SumRange =...

Excel Formula needed [ 1 Answers ]

I am keeping an inventory in Excel. Let's say I start with 100 units of widgets, I take 20 widgets from the shelf, but return 10 wdgets later because I didn't use them. I need a formula that shows the 100, then I want to show that I took 20, and then have Excel show that I returned 10. Can...

View more questions Search