Convert a worksheet formula to a function

In order to rationalise a weekly average to take account that one unit may not be considered a full unit for division purposes (ie if a units revenue is 50% of target revenue chances are that unit can only be considered a 1/2 unit, if two or more are in the same area then instead of considering the total revenue divided by total number of units, we are going to have to consider those half units)

These can be done manually by assigning a number to a column ie: 1,0.75,0.5.0.25,0 and summing the total over the total number of units

the following rough formula is accurate in recreating the result automatically so saving time

The problem lies with the "content" of the formula as the figures used would subject to more scrutiny than the resultant outcome which is fit for its purpose

Can I change this formula

=IF(OFFSET(F4,0,1)"is less than"3000,ROUNDUP((G4/3000),1),1)

in f4 to a function and replace formula (in f4) with "=function1"

Any help appreciated

I'm not sure if I understand your need completely.

But can't you use named range to hold the formula and use the named range in cell to return value?

Edit: Attached sample file.

Hi, thanks Ck78
I could and it would be a perfect solution

Functions have been the solution to a few problems I've been given answers to on this site and I am trying to get a grasp on understanding them, hence this direction


Ive tried

Function Unit(revenue)
   If revenue < 3000 Then
     Unit = Application.Round(revenue / 3000, 1)
    
   Else
     Unit = 1
   End If


End Function

and

=unit(g3)

in cell f3 but cant get the result to round

Sheet was not refreshing perfect when copied and pasted, Ill mark as solved

Tidied up to

Function Unit(revenue)
Const myTarget As Integer = 3000

    If revenue < myTarget Then
        Unit = Application.Round(revenue / myTarget, 1)
    Else
        Unit = 1
    End If

End Function