# Excel formula help!!!!!!!

Closed
del - May 10, 2010 at 05:00 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 10, 2010 at 10:17 PM
I am doing a spreadsheet for a fishing league and would like to know

eg: f6 h6 k6 n6

14lb 2oz 21lb 3oz 14lb 10oz 17lb 12oz

how do i total this at the end, i cant get it to read the nos because of the text and on the total when the oz reach greater than or equal to 16 i want to add another lb and start the oz from scratch, if that makes any sence help!!!!!!!!!!!!! thanks

## 2 replies

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
May 10, 2010 at 09:52 PM
convert lb oz into lb with decimal faces
and then in an empty cell type
=f6+ h6+ k6+ n6
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 10, 2010 at 10:17 PM
You can try this too

Assumptions
Weight is like

xxLb yyOZ
or
XXlb
or
yy OZ

Sample Call

=SumLbOz(f6,h6, k6, n6)

```Function getOZ(myValue As Variant) As Variant
Dim LbOz As Variant
Dim Lb As Variant
Dim Oz As Variant

Oz = ""
Lb = ""
LbOz = Trim(UCase(myValue))

If LbOz = "" Then
getOZ = 0
Exit Function

End If

If (InStr(1, LbOz, "LB") > 0) Then

Lb = Left(LbOz, InStr(1, LbOz, "LB") - 1)

If Len(LbOz) > Len(Lb) + 2 Then

LbOz = Mid(LbOz, Len(Lb) + 1)

If (InStr(1, LbOz, " ") > 0) Then

LbOz = Mid(LbOz, InStr(Len(Lb) + 1, LbOz, " "))

ElseIf (Len(Trim(LbOz)) > 3) Then

Lb = ""
Oz = ""

End If

Else

LbOz = ""

End If

End If

If (InStr(1, LbOz, "OZ") > 0) Then

Oz = Left(LbOz, InStr(1, LbOz, "OZ") - 1)

End If

Lb = Trim(Lb)
Oz = Trim(Oz)

If ((Lb = "") And (Oz = "")) Then

Oz = ""

Else

Oz = (CDbl("0" & Lb) * 16) + CDbl(("0" & Oz))

End If

getOZ = Oz

End Function

Function SumLbOz(ParamArray OtherArgs()) As String
Dim SumLB As Double
Dim SumOZ As Double
Dim temp As Variant

Dim Cell As Object

SumLB = 0
SumOZ = 0

For Each Items In OtherArgs

temp = ""

Select Case LCase(TypeName(Items))

Case Is = "range"

For Each Cell In Items

temp = getOZ(Cell)

If (temp <> "") Then
SumOZ = SumOZ + temp
temp = 0
End If

Next Cell

Case Is = "string"

temp = getOZ(Items)

Case Is = "Double"

temp = getOZ(Items)

Case Else

temp = ""

End Select

If temp = "" Then

MsgBox ("Error Encountered")
SumLbOz = "** ERROR **"
Exit Function

Else

SumOZ = SumOZ + temp

End If

Next Items

SumLB = SumLB + CLng((SumOZ - (SumOZ Mod 16)) / 16)
SumOZ = SumOZ Mod 16

SumLbOz = SumLB & "lb " & SumOZ & "oz"

End Function
```