If Statement

[ndello]

Hi please see my if statement below:
=IF('monthly cover'!K3="January",'broker summary'!B12*-1,IF('monthly cover'!K3="February",'broker summary'!C12*-1,IF('monthly cover'!K3="March",'broker summary'!D12*-1,IF('monthly cover'!K3="April",'broker summary'!E12*-1,IF('monthly cover'!K3="May",'broker summary'!F12*-1,IF('monthly cover'!K3="June",'broker summary'!G12*-1,IF('monthly cover'!K3="July",'broker summary'!H12*-1,IF('monthly cover'!K3="August",'broker summary'!I12*-1,"0"))))))))

Is there any way to continue on to the rest of the months. It will not allow me to go past the eight months, I assume.

Thank you for the assistance.
Neil

 

[Iridium]

As far as I know a formula is limited to seven levels of nested functions so you may be looking for a VBA solution if you want any more - my VBA is a bit too shaky to offer any help with that

 

[Cam]

Hi

Someone may have a nicer looking solution but this should operate properly under your conditions.

=SUMPRODUCT(('monthly cover'!K3={"january","february","march","april","may","june","july","august","september","october","november","december"})*({'broker summary'!B12,'broker summary'!C12,'broker summary'!D12,'broker summary'!E12,'broker summary'!F12,'broker summary'!G12,'broker summary'!H12,'broker summary'!I12,'broker summary'!J12,'broker summary'!K12,'broker summary'!L12,'broker summary'!M12}))

 

[ndello]

Cam, thanks but it did not work. The first broker is lighting up as an error.
Also, will this add the total, because I am trying to select the correct cell to use.

 

[Aladin Akyurek]

On 2002-10-18 10:47, ndello wrote:
Hi please see my if statement below:
=IF('monthly cover'!K3="January",'broker summary'!B12*-1,IF('monthly cover'!K3="February",'broker summary'!C12*-1,IF('monthly cover'!K3="March",'broker summary'!D12*-1,IF('monthly cover'!K3="April",'broker summary'!E12*-1,IF('monthly cover'!K3="May",'broker summary'!F12*-1,IF('monthly cover'!K3="June",'broker summary'!G12*-1,IF('monthly cover'!K3="July",'broker summary'!H12*-1,IF('monthly cover'!K3="August",'broker summary'!I12*-1,"0"))))))))

Is there any way to continue on to the rest of the months. It will not allow me to go past the eight months, I assume.

Thank you for the assistance.
Neil

Is

=-INDEX('monthly cover'!B12:M12,MATCH('monthly cover'!K3,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0))

what you're looking for?
This message was edited by Aladin Akyurek on 2002-10-18 11:21

 

[ndello]

How about this, I want to say: If cell m12 is equal to zero, move left until you find a number not equal to zero and show that number. Can this be done?
Thanks
Neil

 

[ndello]

The index worked great.

Thank you all for your assistance. woo hoo

Neil