Help me with formula

[bhandari]

=MROUND(IF(AF6=100,2*AJ6+2*AK6+AO6,
IF(AF6=101,2*AJ6+2*AK6+AL6,
IF(AF6=102,4*AJ6+AO6,
IF(AF6=103,2*AJ6+2*AK6+4*AO6,
IF(AF6=104,2*AJ6+2*AK6+AO6,
IF(AF6=105,AJ6+AK6+AL6+AM6+AN6+AO6,
IF(AF6=106,2*AJ6+2*AK6+2*AL6,
IF(AF6=107,2*AJ6+2*AK6+2*AL6,
IF(AF6=108,2*AJ6+2*AK6+2*AL6,
IF(AF6=109,2*AJ6+2*AK6+2*AL6,
IF(AF6=110,2*AJ6+2*AK6+2*AL6,
IF(AF6=111,2*AJ6+2*AK6+2*AL6,
IF(AF6=112,2*AJ6+2*AK6+2*AL6,
IF(AF6=113,2*AJ6+2*AK6+2*AL6,
IF(AF6=114,AJ6+2*AK6+AL6+2*AM6,
IF(AF6=115,AJ6,
IF(AF6=116,AJ6+AK6,
IF(AF6=117,AJ6+AK6,
IF(AF6=118,AJ6+AK6,
IF(AF6=119,AJ6+AK6,
IF(AF6=120,AJ6+AK6+AL6,
IF(AF6=121,AJ6+AK6+AL6,
IF(AF6=122,AJ6+AK6+AL6,
IF(AF6=123,AJ6+AK6+AL6,
IF(AF6=124,AJ6+AK6+AL6,
IF(AF6=125,AJ6+AK6+AL6,
IF(AF6=126,AJ6+AK6+AL6,
IF(AF6=127,AJ6+AK6+AL6,
IF(AF6=128,AJ6+AK6+AL6,
IF(AF6=129,AJ6+AK6+AL6,
IF(AF6=130,AJ6+AK6+AL6,
IF(AF6=131,AJ6+AK6+AL6,
IF(AF6=132,AJ6+AK6+AL6+AM6,
IF(AF6=133,AJ6+AK6+AL6,
IF(AF6=134,AJ6+AK6+AL6,
IF(AF6=135,AJ6+AK6+AL6,
IF(AF6=136,AJ6+2*AK6+AL6+AN6,
IF(AF6=137,AJ6,
IF(AF6=138,AJ6+AK6+AL6+AM6,
IF(AF6=139,AJ6+AK6+AL6,
IF(AF6=140,AJ6+AK6+AL6,
IF(AF6=141,AJ6+AK6+AL6+AM6,
IF(AF6=142,AJ6+AK6+AL6+AM6,
IF(AF6=143,AJ6+AK6+AL6+AM6,
IF(AF6=144,AJ6+AK6+AL6,
IF(AF6=145,AJ6+AK6+AL6,
IF(AF6=146,2*AJ6+AK6+2*AL6,
IF(AF6=147,AJ6+AK6+AL6,
IF(AF6=148,AJ6+AK6+AL6+AM6,
IF(AF6=149,AJ6+AK6+AL6+AM6,
IF(AF6=150,AJ6+AK6+AL6+AM6,
IF(AF6=151,AJ6+AK6+AL6+AM6,
IF(AF6=152,2*AJ6+AK6+2*AL6,
IF(AF6=153,AJ6+AK6+AL6+AM6+AN6,
IF(AF6=154,AJ6+AK6+AL6+AM6+AN6,
IF(AF6=155,AJ6+AK6+AL6+AM6+AN6,
IF(AF6=156,AJ6+AK6+AL6+AM6+AN6+AO6,
IF(AF6=157,2*AJ6+2*AK6+AL6,
IF(AF6=158,2*AJ6+2*AK6+AL6,
IF(AF6=159,2*AJ6+2*AK6+AL6,
IF(AF6=160,2*AJ6+2*AK6+AL6,
IF(AF6=161,AJ6+AK6+AL6+AM6,
IF(AF6=162,AJ6+AK6+AL6,
IF(AF6=163,AJ6+AK6+AL6,0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))/1000,0.005)
Errors:
i cant give more than 64 arguments
You have Entered too many arguments to this function

Can i get help with this formula or any alternative way?

Tnx for Looking my Thread

 

[Joe4]

With UDF can I create more than 64 arguments?

You won't need to, as many can be combined!

Can I get help with UDF

Sure, once you answer the questions I asked surrounding this:

If you need more help in writing it, is this always going to be on JUST cell AF6, or multiple cells?
If multiple cells, are they always in column AF?

 

[bhandari]

it is a merged cell with 3 cells= AF6,AF9,AF12 up to.AF78=this cells should have same formula

 

[Joe4]

it is a merged cell with 3 cells

Can we get rid of the merged cells? I am hesitant to even attempt any VBA code when I see "merged cells".
Merged cells are very, very bad, especially for programmers! They cause all sorts of issues, and should really be avoided.

Here is a link that talks about alternatives:
https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[Joe4]

OK. I have started this for you. I have done for values 100-113. You can see how for values that have the same calculation (100 and 104, and then 105 - 113), I showed how you can lump them together so you do not need to repeat the calculations). You should be able to detect the pattern and do the rest (it will be a good test). Just keep adding for "Case" statement blocks before the "Case Else" (which is just a catch-all for everything else not matching one of the values being checked for).

Function MyCalc(rng As Range) As Double
'   rng = single cell in column AF

    Application.Volatile
    
    Dim r As Long

'   Make sure column is AF and only one cell being called
    If (rng.Count > 1) Or (Left(rng.Address, 4) <> "$AF$") Then
        MyCalc = 0
        Exit Function
    End If
    
'   Get row number
    r = rng.Row
    
'   Perform calculation based on value of cell
    Select Case rng.Value
        Case 100, 104
            MyCalc = 2 * Cells(r, "AJ") + 2 * Cells(r, "AK") + Cells(r, "AO")
        Case 101
            MyCalc = 2 * Cells(r, "AJ") + 2 * Cells(r, "AK") + Cells(r, "AL")
        Case 102
            MyCalc = 4 * Cells(r, "AJ") + Cells(r, "AO")
        Case 103
            MyCalc = 2 * Cells(r, "AJ") + 2 * Cells(r, "AK") + 4 * Cells(r, "AO")
        Case 105 To 113
            MyCalc = 2 * Cells(r, "AJ") + 2 * Cells(r, "AK") + 2 * Cells(r, "AL")
        Case Else
            MyCalc = 0
    End Select
                
End Function

You would then just use it like any other function in Excel, i.e.

=MyCalc(AF6)

 

[bhandari]

Working Like a charm Tnx Joe4

 

[Joe4]

You are welcome!

 

[bhandari]

I am trying to do it in Excel,I am not Getting Result
I have Modified Column Reference
Can You Please cross check this

    Dim r As Long

'   Make sure column is AF and only one cell being called
    If (rng.Count > 1) Or (Left(rng.Address, 4) <> "$O$") Then
        MyCalc = 0
        Exit Function
    End If
    
'   Get row number
    r = rng.Row
    
'   Perform calculation based on value of cell
    Select Case rng.Value
        Case 100, 104
            MyCalc = 2 * Cells(r, "S") + 2 * Cells(r, "T") + Cells(r, "X")
        Case 101
            MyCalc = 2 * Cells(r, "S") + 2 * Cells(r, "T") + Cells(r, "U")
        Case 102
            MyCalc = 4 * Cells(r, "S") + Cells(r, "X")
        Case 103
            MyCalc = 2 * Cells(r, "S") + 2 * Cells(r, "T") + 4 * Cells(r, "X")
        Case 105 To 113
            MyCalc = 2 * Cells(r, "S") + 2 * Cells(r, "T") + 2 * Cells(r, "U")
        Case Else
            MyCalc = 0
            
    End Select
                
End Function

 

[Joe4]

What column are you trying to check?
If it is column O, then you want to take the left 3 characters, not 4, i.e.

(Left(rng.Address,[COLOR=#ff0000][B] 3[/B][/COLOR]) <> "$O$")

 

[bhandari]

Thank you Joe4 for solving my issue
Actually cell have 3 characters only
Not 4
Y previously taken 4?

 

[Joe4]

rng.address returns the cell address, not the cell contents.

Originally, we were limiting it to cells in column AF. If you return the address of any cell in AF using rng.address, it will return something like $AF$100 (for cell AF100).
So, we were checking the first four characters of the address for "$AF$.

However, now that you are checking row O, the column reference is one character less. So we are looking for "$O$", which is only 3 characters.

Does that make sense now?