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

 

[t0azt]

Make a table with the number and desired result for each one and do a vlookup?

 

[bhandari]

Is it not possible to use more than 64 arguments?

It's a cell reference that cell value changing by triggering if the value is change this formula result must appear in AF6

I don't think it will work with making table

 

[Gerald Higgins]

A VLOOKUP or something similar is DEFINITELY the way to go here.

 

[Joe4]

Is it not possible to use more than 64 arguments?

No, as Excel is telling you.
If you ever hit this limit, it is a good indication that you are going about it the wrong way.

Another option may be to create your own function in VBA (User Defined Function) to do it. If you use CASE statements in VBA, you can lump all the different values of AF6 together that result in the same calculation.

 

[bhandari]

No, as Excel is telling you.
If you ever hit this limit, it is a good indication that you are going about it the wrong way.

Another option may be to create your own function in VBA (User Defined Function) to do it. If you use CASE statements in VBA, you can lump all the different values of AF6 together that result in the same calculation.

Can u help me out with case statementIf I get statement sentence

I will fallow that

 

[Joe4]

Here are some links on creating UDFs:
http://www.excel-easy.com/vba/examples/user-defined-function.html
https://www.thespreadsheetguru.com/blog/create-custom-excel-user-defined-functions-with-vba

See here for help on the CASE statement: https://www.techonthenet.com/excel/formulas/case.php

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?

 

[t0azt]

I still think Vlookup is your best option here.... make a sheet with the values in column A and the formula from your if statements in column B
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]=2*AJ6+2*AK6+AO6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]=2*AJ6+2*AK6+AL6[/TD]
[/TR]
</tbody>[/TABLE]

etc.
then your formula just becomes

=VLOOKUP(AF6,Sheet1!$A$1:$B$149,2,0)

modify to fit your data.

 

[Gerald Higgins]

And here's another thought.

Your original formula contains 64 IF statements.
But there are only 14 unique TRUE arguments.
For example 22 of the IF statements include an instruction to return the result of
AJ6+AK6+AL6.
For example 10 of the IF statements include an instruction to return the result of
AJ6+AK6+AL6+AM6.

What this suggests to me is that there are other ways which you could possibly greatly simplify your challenge.

 

[bhandari]

Can I get help with UDF

I hope it works fine with state statement

With UDF can I create more than 64 arguments?