urgent...convert sum into max

[Tami]

sorry for the urgency but i thought i posted this message yesterday afternoon
and assumed that i could make some headway this am...but it doesn't appear to
have posted.

Question:
is there a way to convert a forumula that looks like this =a1+a2+a3 to
Max(a1,a2,a3)? also can i convert =sum(a1:a3) to max(a1:a3).

in the first case it has 3 entries, in another instance it might have 4, 5,
etc.

do i use the subtitute function?

 

[Ms-Exl-Learner]

For Converting =sum(a1:a3) to =Max(a1:a3)
Select the range and press Cntrl+H in Find what box: mention SUM and in
Replace with: type MAX and give Replace All. Now the Sum function will be
converted to Max function.

For Converting =a1+a2+a3 to =MAX(A1,A2,A3)
Select the range and press Cntrl+H in Find what box: mention = and in
Replace with: type =MAX( and give Replace All. Now the Sum function will be
converted to =MAX(A1+A2+A3)
Again in Find what box: mention + and in Replace with: type , (comma) and
give Replace All. Now the =MAX(A1+A2+A3) will be converted to =MAX(A1,A2,A3).

Remember to Click Yes, if this post helps!

 

[Fred Smith]

Substitute works on data, not formulas. Use the Replace command to change
formulas. The actual steps required depend on your version of Excel. As
always, identifying the version of Excel you are using will help you get
more accurate answers, especially when they are urgently needed.

Regards,
Fred

 

[Tami]

thanks, ok, so i get the first one...its using the edit/replace function to
modfiy the formula but i actually need a macro (or a formula) to do the steps
and put the new formula in another cell.

so in other words the sum formula =sum(a1:a3) is in cell A7....i need to put
the Max formula =max(a1:a3) in cell B7 by using a macro or by using a
forumula that looks at cell a7

ditto if the formula is =a1+a2+a3.

suggestions? should i use "replace", "subtitute", use a formula or use a
macro?

thanks for any and all suggestions

 

[Fred Smith]

Why not just enter the Max formula in B7? Presumably you entered the Sum
formula in A7. Do the same for B7.

There is no formula which can look at A7 to find the Max. You have to look
at the original data (ie, a1:a3).

Regards,
Fred