How would you write this formula in Excel?

Answer 1

*Note: You have probably forgotten or got an answer for this years, ago. The reason your question has taken so long to answer is that you put the description into the Answer area, meaning the question was deemed to have an answer in it. A description should go into the discussion area. Your original question and an answer are below.

[Extra Information -

I have an excel problem and I need a formula to perform the task. Here is the situation: I have two tables in excel; a table full of dates and a table showing the week numbers. I have been given some dates in this format "28/01/2010". I need excel to work out which week number (in the year) this date falls into and then put a "1" in that week row.

For example: if I get given the date "28/01/2010", excel will look at that date and put a "1" in the week 4 row because that date is found in week 4 of the year. If then I get given another date: "25/01/2010", excel will add another 1 to the week 4 row, meaning week 4 row will display "2". All together, I have been given lots of different dates and I want excel to sort all the dates into their corresponding week numbers within the year. This is ultimately to create an automatic system where the dates can be typed into a excel table, and then they can be turned into week number type form in the other table.

I hope this is clear enough. :)]

You could take a different approach. Use the WEEKNUM function to work out which week a date is in. So if the date you wanted to find the week for was in cell A2, beside the date you could have =WEEKNUM( A2 )

Then you could use the FREQUENCY function to help set up a frequency table to show how many dates are in each week. This would have to be done with an array function. Without seeing your actual set of data, it is impossible to give specific cell references. You would set up a column with the week numbers, having just the number, so 1 for Week 1, 2 for Week 2 etc. In a column beside that, you would have your FREQUENCY function to calculate the amount for each week, based on the source data from the set of week numbers that you have calculate. You will need to do a little research on the FREQUENCY function and also on array formuals.

Answer 2

For all questions like this it really is best to use the help feature built into Excel. This is what it is there for! Click on the help icon and type in 'IF function'.

I will try to help you too though.

The IF function comprises of 3 parts. The first is a logical test to be carried out where the answer is either true or false. The second part is what to do if the answer is true. The third part is what to do if the answer is false.

An example: (open up Excel and follow this through to understand it!)

Imagine you have a cell (let's make it A1) with the number 6 (formatted as a number) in it. Say you wanted to know if this number was greater than 5. In cell B1 you could write:

=IF(A1>5,"Greater than 5!","Not greater than 5!")

That is an example of an if statement. It must start =IF(

then:

* you put the logical test in - here we are testing if A1>5

* you put a comma

* now you put what to do if it is true - we are making it write Greater than 5! We put this inside quotes to signal it is text (a string actually).

* you put a comma

* you put what to do if false - we make it write Not greater than 5!

* put a closing bracket - )

Cell B1 now should read Greater than 5!

Now go back to cell A1 and change the number to, for example, 2 to see the difference.