Can the CHOOSE function be used to have different calculations as its values?

[e4excel]

Can the CHOOSE function be used to have different calculations as its values?

I have a requirement in which I need to use different calculations based on the MODE.
I have the following as the MODES of Payment:

YLY
HLY
QLY
ECS
SGL

So, in the COlumn N from N2 till N6 contains different modes then can I use it like below:

= choose(N2, YLY cal, HLY cal, QLY cal, ECS cal, 0)

Is there a wauy of suing DEfined Names in the CHOOSE function..

Regards
e4excel

[DonkeyOte]

Presumably by MODE you mean mode of payment rather than MODE (as in the calculation)

If you're saying these are strings (N2:N6) then yes you can use CHOOSE but you need to Match first to generate the index_num for CHOOSE

Please Login or Register  to view this content.

Of course if N2 is not any of these options then the above would error - you can use an appropriate handler to account for this

Assuming pre XL2007 and a default of 0

Please Login or Register  to view this content.

[e4excel]

Actually, I need to do different calculations dynamically depending on the value of Mode in the cell N2...

[e4excel]

Dear DO,

please ignore my last message I whad not checked your reply..

Regards
e4excel

[e4excel]

Presumably by MODE you mean mode of payment rather than MODE (as in the calculation)

Yes MOde is Mode of payment and not the MODE function, I am sorry I should have been more explicit

If you're saying these are strings (N2:N6) then yes you can use CHOOSE but you need to Match first to generate the index_num for CHOOSE

=CHOOSE(MATCH(N2,{"YLY","HLY","QLY","ECS","SGL"},0),YLY Cal,HLY Cal,QLY Cal,ECS Cal,SGL Cal)

Thanks a lot DO as always you are just amazing..!

[= CHOOSE(MATCH(N2,{"YLY","HLY","QLY","ECS","SGL"},0), ]

I used to always miss the coloured portion in your code and therefore was never comfortable using the CHOOSE function, but now that you explained it so well..

I can use it..with good efect..

Thanks a Lot^lot

Regards
e4excel

[e4excel]

[B]
Is there a way of using DEfined Names in the CHOOSE function..

I tried Using Defined Names but it does not work so is there a workaround?
As some calculations can consume lot of nestings..

Regards
e4excel

[DonkeyOte]

@e4excel, the above provides very little in the way of meaningful information... along the lines of:

I tried a formula - it didn't work - could you fix it ?

You need to provide a lot more info. and preferably a sample file with Defined Names in place etc...

[e4excel]

Dear DO,

I always prefer to attach a sample file however due to the senstive nature of information, i an unable to upload a file....

This work is related to Insurance Policy Databse..

Regards
E

[e4excel]

Thanka lot DO,

I made a mistake it works with Defined Names as well..

I was making some silly mistake somewhere!

But it works and moreover i have now understood more about CHOOSE function..