IF formula help

[LeslieO via OfficeKB.com]

Hi,

I really hope there is an easy way to fill cells down without retyping this
formula:

=IF(J117="","",IF(P1014>=100000,J117*0.019+0.3,IF(P1014>=10000,J117*0.022+0.3,
IF(P1014>=3000,J117*0.025+0.3,IF(P1014>=0.01,J117*0.029+0.3,)))))

The problem is that the P1014, which stays the same in every formula keeps
adding one to be P1015, P1016, etc. when I fill down. I've even highlighted
10+ cells with the P1014 (which remains static throughout the formula), and
excel insists on wanting to add ONE to equation.

I really appreciate any help. Otherwise, I'll be doing this manually for 1000
cells.

Thanks,
Leslie

 

[Guest]

If P1014 is to be "constant", type as $P$1014 (See HELP on relative and
absolute addressing).

 

[Pete_UK]

Put a dollar symbol in front of each 1014, like so:

=IF(J117="","",IF(P$1014>=100000,J117*0.019+0.3,IF(P
$1014>=10000,J117*0.022+0­.3,IF(P$1014>=3000,J117*0.025+0.3,IF(P
$1014>=0.01,J117*0.029+0.3,)))))

That way it will not change when you copy the formula down. Have a
look in Excel Help for Absolute (and Relative) addresses/references.

Hope this helps.

Pete

 

[LeslieO via OfficeKB.com]

Oh my gosh Toppers and Pete, I Love YOU!

Put a dollar symbol in front of each 1014, like so:

=IF(J117="","",IF(P$1014>=100000,J117*0.019+0.3,IF(P
$1014>=10000,J117*0.022+0­.3,IF(P$1014>=3000,J117*0.025+0.3,IF(P
$1014>=0.01,J117*0.029+0.3,)))))

That way it will not change when you copy the formula down. Have a
look in Excel Help for Absolute (and Relative) addresses/references.

Hope this helps.

Pete

Hi,

[quoted text clipped - 17 lines]

 

[Pete_UK]

Hey, now, steady on !! It wasn't that good !!

But thanks for feeding back.

Pete

Oh my gosh Toppers and Pete, I Love YOU!

Put a dollar symbol in front of each 1014, like so:

That way it will not change when you copy the formula down. Have a
look in Excel Help for Absolute (and Relative) addresses/references.

Hope this helps.

Pete

[quoted text clipped - 17 lines]