Jump to content


Photo

Formula for months

formulas IF

  • Please log in to reply
9 replies to this topic

#1 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 25 March 2014 - 06:20 PM

I'm trying to figure out this formula for myself for the last few days and can't get it. I attached an example. Thank you!

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 25 March 2014 - 08:23 PM

Hi Paula,

 

To make the formula simpler, I have added formulas in row 3 to get the numbers of each month.

 

numerology2.png

 

The formula in cell G5 will now be something like:

=IF(G$3=1,$F6,IF(G$3>=$A$5,2,1)+F5)

 

This formula uses the IF function along with absolute references to model the logic that you need.

 

The IF function is covered in depth in the video lesson: Expert Skills Lesson 3-5: Use the IF logic function.

 

The tricky part of this formula is the absolute references (eg. G$3) that are needed to keep the formulas sensible when filled across the cells.  For a more in-depth look at absolute cell references, see the video lesson: Essential Skills Lesson 3-12: Understand absolute and relative cell references.

 

This formula models the following logic:

 

If the month number is 1 (January), show the number in column F for the row below this one.

 

Otherwise...

 

If the month is greater than the year of birth, add 2 to the previous cell.

If the month is less than the year of birth, add 1 to the previous cell.

 

As usual, I have attached a copy of your spreadsheet with the new formulas applied:

 

Attached File  numerology2.xlsx   14.96KB   170 downloads

numerology2.png

 

If you need any more assistance, please feel free to reply.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 26 March 2014 - 03:13 AM

Hi Jonathan,

Thank you for your help.

The way you did it was not exactly what I needed but I was able to learn from your example and create the chart I needed. The problem is now that I have the correct numbers showing up there is one line that is coming out wrong and I'm not sure why. Can you make sense of why it's coming out wrong? I am dumb founded. The first range has all the formulas and the second range shows how it's suppose to look. Thank you

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 26 March 2014 - 12:15 PM

Hi Paula,

 

Your formula is as follows:

=IF(I$7=1,$F14,F13+I$7)

 

This translates as:

 

IF the month is January, get the number for the next year.

Otherwise, get the number for the year and add the month onto it.

 

The formulas seem to be applying this logic consistently, so if this isn't the logic that you're trying to model, you will need to explain how it should be working differently.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 26 March 2014 - 01:09 PM

Hi Jonathan,

All the formulas are right except when it gets into a year that has a zero at the end. Like 1970 and 1980. The year before adjusts everything correctly but on those zero years it doesn't. 

 

So I guess I would need a formula that would take into account the Zero years and just add 1 to the number after Jan.

 

I hope this makes a little more sense now. If not I'll try again. Thanks again for all your help, Paula

 

Attached Files



#6 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 26 March 2014 - 01:18 PM

What would be better is if it would take from the 1970 #for the yr ( from our example) and minus one. That might be it. but I don't know how to formulate that. thank you.



#7 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 26 March 2014 - 01:24 PM

But now thinking of it if we did that than Nov and Dec in 1970 would be minus one too,  and I don't want that. the numbers are right the way they are.



#8 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 26 March 2014 - 01:33 PM

So I guess if we can some how just take the # for the yr  minus 1 and add the month number for Feb it would be 2 than I would get the number I needed 48 but if it changes the Nov and Dec cells in the row 1970 it wouldn't work.

 

Do you think just adding one to the rows after Jan would be better?

 

One  row is combined into two year dates. So, it would be in the first row in the range Nov and Dec 1965 than Jan - Oct 1966



#9 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 26 March 2014 - 02:26 PM

Hi Paula,

 

There are several ways that you could achieve what you're looking for.  The problem here isn't really with the formulas, but with the definition of the logic that you're trying to apply - it isn't easy to write a formula if you don't know what rules it should be using!

 

The simplest solution that I can think of to get the numbers that you want is to leave your formulas in column G alone and then use the following in the rest of the cells:

=IF(H$7=1,$F10,G9+1)

 

This seems to return the results that you need, but again, it's down to you to decide whether this is the logic that you're looking for.

 

As usual, I have attached the spreadsheet with the formulas applied.

 

Attached File  numerology3.xlsx   17.53KB   189 downloads

numerology3.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#10 Paulajewels

Paulajewels

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 26 March 2014 - 03:04 PM

Thank you for your help I think this might be it. 







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users