Adding round to existing formulas

[Bryz]

Hi all,

I'm trying to update a series of workbooks that are almost entirely formula based and I need to add the round function to all the formulas, but the formulas are all the same. Is there an easier way than adding round in manually?

I've attached a simplified example, so in this case if I would like to add the round function to the totals in columns T-X

Thanks!

 

[jayalaxmi]

Hi all,

I'm trying to update a series of workbooks that are almost entirely formula based and I need to add the round function to all the formulas, but the formulas are all the same. Is there an easier way than adding round in manually?

I've attached a simplified example, so in this case if I would like to add the round function to the totals in columns T-X

Thanks!

is it answer u needed??

Pls see the attachment

 

[Bryz]

is it answer u needed??

Pls see the attachment

Not really, looking for a way to make it "=round(sum(...),1) for each of the totals without going through each cell and typing it in, because the actual workbook has hundreds of them

 

[jayalaxmi]

ok u mean u need one formula and that should be applied for every cells???

 

[jayalaxmi]

c this if this is working for u...otherwise any body else vl help u....

 

[Bryz]

Yeah basically. The best I've come up with so far is extracting the formula and then using concatenate to add round in, then turning it back into a formula.

 

[jayalaxmi]

Yeah basically. The best I've come up with so far is extracting the formula and then using concatenate to add round in, then turning it back into a formula.

can u pls share it pls...

 

[Bryz]

I've modified it to automatically add the round straight away.

Option Explicit

Function GetFormula(rng As Range) As String
  GetFormula = rng.Formula
End Function

Function AddRound(rng As Range) As String
  AddRound = "=ROUND(" & Right(rng.Formula, Len(rng.Formula) - 1) & ",1)"
End Function

 

[jayalaxmi]

ohhhhh u had used VBA ...thanks for sharing bryz....but the thing is I don't know VBA....anyways I am happy that u got ur output....

 

[Khalid NGO]

Hi Bryz,
Other than VBA, you can use the following formula in T3 with some amendment in your sheet layout:
=SUMIF($B$1:$O$1,T$1,$B3:$O3)+SUMPRODUCT((MID($Q$1:$R$1,FIND(" ",$Q$1:$R$1)-8,10)=T$1)*($Q3:$R3))

copy right and down.

Note:
You have to unmerge cells so the criteria can be matched.
see the attached yellow cells.

Regards,

 

[jayalaxmi]

Hi Bryz,
Other than VBA, you can use the following formula in T3 with some amendment in your sheet layout:
=SUMIF($B$1:$O$1,T$1,$B3:$O3)+SUMPRODUCT((MID($Q$1:$R$1,FIND(" ",$Q$1:$R$1)-8,10)=T$1)*($Q3:$R3))

copy right and down.

Note:
You have to unmerge cells so the criteria can be matched.
see the attached yellow cells.

Regards,

wow Khalid ji..kya baat hai...

 

[Khalid NGO]

wow Khalid ji..kya baat hai...

Thanks Jaya,
Glad you like it.