Stupid excel formula help

[Jono]

Excel 2003 if it matters.

I have something very basic I want to do:
Formulas for a column. In cell A3 I have:
=IF($C$1-$C$3>=30, "something", "else")

This works fine. The values in C1 and C3 are both dates.

But now what I want to do is apply a formula like this to all cells in column A. Always comparing C1 to CX. So ...

=IF($C$1-$C$3>=30, "something", "else")
=IF($C$1-$C$4>=30, "something", "else")
=IF($C$1-$C$5>=30, "something", "else")
=IF($C$1-$C$6>=30, "something", "else")
=IF($C$1-$C$7>=30, "something", "else")
=IF($C$1-$C$8>=30, "something", "else")

etc etc.

I'll probably add in a couple of ELSE IFs in there, but - I can't get it to apply the formula automatically to column A - It always uses
=IF($C$1-$C$3>=30, "something", "else")

The second cell never changes; I thought it would work just by clicking in the bottom right dot of cell A3 and dragging down.

Selecting C3 and C4 and doing the above makes it alternate between 3 and 4 for the whole column.

Sorry if this makes no sense, but I'm hoping it does.

Just has to always look at C1 for the first value in the calculation, then C<whatever> for the corresponding cell A<whatever>

 

[Tomsk]

remove the $ symbols

the first $ locks the column reference.

the second $ locks the row reference

For your formula yoyu need to remove the second $ reference, from the formula in A3 and then copy/fill down.

Cell A3 : "=IF($C$1-$C3>=30, "something", "else")"

 

[Jono]

Duh!

Thanks very much - thought it had to be something simple. I've done it before loads of times when I was at uni. Haven't touched excel since.

 

[Jono]

Hi,

Can anyone help me make a compound IF formula?
Cell C1 is today's date. 01/06/2009

I need to compare (for example) C3 to C1 (also a date) and fill cell A1 with a value based on the difference between the two.

If the difference >= 90 something
OR difference >= 30 something else
or >= 15 another thing
else yet another thing.

Is this possible? and if so what's the formula because I can't work it out

 

[SuperBeast]

Search for 'or statement' in excel help. It works something like 'or(condition 1, condition 2, ...) I think you are limited on the number of conditions, but hopefully you won't need that many. then you can write your if statement something like =if(or(condition 1, condition 2),"something", "else")

 

[miniyazz]

Haha, I never would've thought of that
My initial response to a problem like that is add another IF statement in the false part of the first one
For the record, you can have up to 31 nested formulae in Excel 2007. It's less in older versions.

 

[Dolph]

Hi,

Can anyone help me make a compound IF formula?
Cell C1 is today's date. 01/06/2009

I need to compare (for example) C3 to C1 (also a date) and fill cell A1 with a value based on the difference between the two.

If the difference >= 90 something
OR difference >= 30 something else
or >= 15 another thing
else yet another thing.

Is this possible? and if so what's the formula because I can't work it out

=if(A1>=90, something, if(A1>=30, something else, if(A>=15, another thing, yet another thing)

should do it.

you test each level, and if the evaluation is true, it stops and displays the result, else it moves to the next if statement. As long as you get the order right, it's fine.

 

[Jono]

Cheers Dolph, that sort of thing seems to have sorted it - found the example in Help file eventually. Took a bit of messing around to do, but got it