Calculating a field based on what is in another column
 

Hi everyone,

I have a 3 column spreadsheet.

Amount Frequency Weekly$ (Calculated)

1200 F 600 i.e. divides it by 2 if F
100 W 5200 i.e. times it by weeks in
year
5200 Y 100 ie divides it by weeks in
year

As you can see, I want the Weekly$ amt to be calculated based on whether the
Frequency is W (Weekly), F (Fortnightly) or Y (Yearly).

any ideas as to what to do ?

cheers,

Adam

Assuming your data is in columns A and B, one way:

=A1*INDEX({0.5,100,0.01},1,MATCH(B1,{"F","W","Y"}, 0))

HTH

Alan P.

"acs68" wrote in message
...
Hi everyone,

I have a 3 column spreadsheet.

Amount Frequency Weekly$ (Calculated)

1200 F 600 i.e. divides it by 2 if F
100 W 5200 i.e. times it by weeks in
year
5200 Y 100 ie divides it by weeks
in year

As you can see, I want the Weekly$ amt to be calculated based on whether
the Frequency is W (Weekly), F (Fortnightly) or Y (Yearly).

any ideas as to what to do ?

cheers,

Adam

=a1/2*(b1="F")+a1*52*(b1="W")+a1/52*(b1="Y")

"acs68" wrote:

Hi everyone,

I have a 3 column spreadsheet.

Amount Frequency Weekly$ (Calculated)

1200 F 600 i.e. divides it by 2 if F
100 W 5200 i.e. times it by weeks in
year
5200 Y 100 ie divides it by weeks in
year

As you can see, I want the Weekly$ amt to be calculated based on whether the
Frequency is W (Weekly), F (Fortnightly) or Y (Yearly).

any ideas as to what to do ?

cheers,

Adam

=IF(B2="F",A2/2,IF(B2="W",A2*52,IF(B2="Y",A2/100,"")))

Vaya con Dios,
Chuck, CABGx3



"acs68" wrote:

Hi everyone,

I have a 3 column spreadsheet.

Amount Frequency Weekly$ (Calculated)

1200 F 600 i.e. divides it by 2 if F
100 W 5200 i.e. times it by weeks in
year
5200 Y 100 ie divides it by weeks in
year

As you can see, I want the Weekly$ amt to be calculated based on whether the
Frequency is W (Weekly), F (Fortnightly) or Y (Yearly).

any ideas as to what to do ?

cheers,

Adam