could someone plse help me with this formula

E

eleanor

I am currently using this formula
=IF((IF(($F$65*$C66/$C$84-A66)<0,TRUE,FALSE))=TRUE,MROUND(($F$65*$C66/$C$84-A66),-1000),MROUND(($F$65*$C66/$C$84-A66),1000))

Very often, I need to copy it to other cell and then change $f$65 and
$c66 and $c$84 and A66 to the necessary.

And I realised I need to change it 3 times in that long string of
formula. Sometime in a hurry, I forgot to change all of it (3 time).

Is there a way where, the middle and last portion of the long formula
will change automatically the moment I just change the beginning of the
formula.

Thank you very much for your assistance. Truly appreciate it.

Regards
 
F

Frank Kabel

Hi
one way would be to change your formula to
=MROUND(($F$65*$C66/$C$84-A66),((($F$65*$C66/$C$84-A66)>=0)-1)*1000)
to reduce it two two instances.

Frank
 
N

Niek Otten

Hi Eleanor,

It would be a lot easier if you used one extra cell, with the formula:

=$F$65*$C66/$C$84-A66

Now if this formula ia in A1, your other formula would be

=mround(a1,if(a1>0,1000,-1000))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
J

Jonathan Cooper

What I do to work around this problem is use 'replace' (ctrl + h).
Copy the formula down. Select that cell and at least one more. then
hit ctrl+h, then type in that you want to find $f$65 and replace it
with $f$72 (or whatever). hit the replace all button and your good to
go.
 
N

Niek Otten

Please note that $f$651 will be replaced with $f$721. May be what the OP
wants, maybe not

Eleanor,

If you have any experience in writing User Defind Functions, that would be
the best solution.
Post again if you still have problems

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
E

eleanor

Dear Niek Otten

Thanks. Yup, it does lighten my load, speed up the process and likely
to make less error. As the spreadsheet is already very big with many
other cells, columns and rows, I would like to minimize creating extras
cell. Is there another method? What is writing User Defined Function?
If there isn't any other option, I would have to adopt your below
method.
Thanks
Eleanor
 
E

eleanor

Dear Niek

I have created an example. Is there a way I could forward the exce
file to you so you could have a better understanding.
Thanks
 
T

Tushar Mehta

=MROUND(A3,B3) is the same as =ROUND(A3/B3,0)*B3 as long as the
arguments to MROUND have the correct sign. So, the simplified formula
that will work for positive *or* negative numbers is =ROUND(($F$65*
$C66/$C$84-A66)/1000),0)*1000

I guess this is yet another example of the poor quality of the
functions in the Analysis ToolPak.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top