could someone plse help me with this formula

  • Thread starter Thread starter eleanor
  • Start date Start date
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
 
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
 
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
 
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.
 
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
 
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
 
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
 
=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
 
Back
Top