Constants in formulae

[Alc]

I have a formula in an Excel cell, as follows

=IF([COLOR=black][B]Y8[/B][/COLOR]=1,(1.2*([COLOR=navy][B]AE10[/B][/COLOR])),IF([B][COLOR=black]Y8[/COLOR][/B]=2,(0.8*([B][COLOR=navy]AE10[/COLOR][/B])),IF([COLOR=black][B]Y8[/B][/COLOR]=3,(0.4*([COLOR=navy][B]AE10[/B][/COLOR])),IF([B][COLOR=black]Y8[/COLOR][/B]=4,0))))

The values in bold, black font change from row to row.
The values in bold, blue font need to remain constant, as they are defaults that will apply equally to every row.

Is there a way in which this could be written such that the blue values don't change when I copy and paste the formula over, say, 500 rows? At present, if I do that row 500 refers to AE512 rather than the desired AE10.

I've been searching for help on this but I'm unsure even what terminology to look for.

Any suggestions are much appreciated.

 

[rainman89]

I think if you use $AE$10 it will keep it the same for every row

 

[Alc]

Perfect! Falls into the 'simple once you know how' category.

Thanks very much, that's saved me a lot of time.

 

[TomH]

Alc:

Be sure to check out the F4 key for cycling through the possibilities of locking the cell addresses. Type a cell reference into a formula... click on that cell reference... hit the F4 key a few times and watch what happens. It works for ranges as well... e.g. A1:Z26 . Click on one or the other or highlight both and try it.

You're going to find many times in which you need to copy across a row in which you want to lock the row but not the column... or the other way around. The term used for the default Excel copy/paste mode is 'referential' addressing. It's a huge help once you start applying it.

 

[Alc]

Thanks a lot, Tom. I'll give it a try as soon as I get the chance.

 

[Brianwarnock]

Every Excel user should be familiar with the difference between relative and absolute references as descibed in help.

Brian

 

[TomH]

I'm not sure what you mean, Brian.

 

[Brianwarnock]

They should understand fully the difference between

A1
$A1
A$1
$A$1

and the extension of this into referencing ranges.
Dragging formulae down/along, copying, inserting/deleting rows/columns are all effected by which type of referencing is used.

Brian