Excel Newbie Questions - Desparate

[sadromeo]

Hi all,

1)I have a question about the $ used in cell references. I understand that $C$1 refers to cell C1 regardless of where the cell with the active forumla is located but what exactly would $C1 refer to??

2)Also, what exactly does this reference BE$1 mean?

3)How does the YEAR and Month functions work exactly? I looked at the MS Help file for excel and it says in an example "MONTH(366) = 12 and MONTH(367) = 1", how does that work?

Here is the formula that I'm trying to figure out. The questions above relate directly to this formula:

=IF(AND(ISBLANK($AI2),ISBLANK($AJ2)),$AH2/12,0)
+IF(AND($AC2>=BE$1,$AB2<=BE$1),$AA2/(12*(YEAR($AC2)-YEAR($AB2))+MONTH($AC2)-MONTH($AB2)+1),0)
+IF(AND($AJ2>=BE$1,$AI2<=BE$1),$AH2/(12*(YEAR($AJ2)-YEAR($AI2))+MONTH($AJ2)-MONTH($AI2)+1),0)
+IF(AND($AS2>BE$1,$AR2<=BE$1), $AQ2/(12*(YEAR($AS2)-YEAR($AR2))+MONTH($AS2)-MONTH($AR2)+1),0)

I understand the IF and AND functions but I'm lost as to what $AI2 and BE$1 is referencing and how YEAR and MONTH is used in this formula? Can anyone help???

Thanks thanks so much!
Chad

 

[ETID]

If you goto tools>auditing>trace precedents or dependents
excel will point to all cells referenced by this formula.

 

[petrosky]

Hi Chad,

The $ signs in the cell references mean that it is an absolute reference instead of a relative reference.

IE. if I have =$b$1 and copy this down the row it will remain the same (absolute)
if I have =b1 and drag it down the row it will become b2,b3,b4 etc

The other references you mention &quot;BE$1&quot; is the first row of the BE column...quite a long way to the right of the &quot;standard&quot; excel view.

YEAR and MONTH functions are summed up pretty well in Excle help files.

HTH.

Peter Remember- It's nice to be important,
but it's important to be nice

 

[hopelessliar]

where you have, for example, BE$1, the row (ie 1) is absolute and will not be changed. So if the formula in A1 read simply '=BE$1' and you copied it to, say, C3 the forula would become '=BG$1' since the column part of the reference is not absolute and changes with location, but the row part is fixed at 1.

I hope this is relatively clear.

The date functions work roughly like this:

each day has a number assigned to it. The first day (I think) was Jan01 1900. When you do month() excel checks which month the number falls in. month (366) is a special case. Excel thinks that 1900 was a leap year, hence 366 days and 366 falls in December, actually this wasn't a leap year but hey ho, never mind the reasons for this now. In short, all excel dates and times are represented by numbers. So, for example 367.5 corresponds to 12noon on the 1st Jan 1901. 367.75 is 6pm on the same day and so on.

Again, I hope this helps you.