IF formula help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LeslieO - 09 Mar 2007 18:49 GMT

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

Reply to this Message

Toppers - 09 Mar 2007 19:04 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
> Thanks,
> Leslie

Reply to this Message

Pete_UK - 09 Mar 2007 19:05 GMT

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]
> --
> Message posted viahttp://www.officekb.com

Reply to this Message

LeslieO - 09 Mar 2007 19:18 GMT

Oh my gosh Toppers and Pete, I Love YOU!

>Put a dollar symbol in front of each 1014, like so:
>
[quoted text clipped - 14 lines]
>> --
>> Message posted viahttp://www.officekb.com

Reply to this Message

Pete_UK - 09 Mar 2007 19:24 GMT

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

But thanks for feeding back.

Pete

> Oh my gosh Toppers and Pete, I Love YOU!
>
[quoted text clipped - 21 lines]
>
> - Show quoted text -

Reply to this Message