Running the right If statement in column cell formula. 1

[TheAceMan1]

Howdy All ... The machine in question has XL 2003 with Vista.

What I need to do is execute one of two [blue]If statements[/blue] (which work fine independently), depending on two input columns. Following is sample Data:

[pre] G H I J K
Yen US_Dollar Hrs Days YenTlt
----- --------- --- ---- ------
85000 10 850,000
$50.00 8 6 281,928[/pre]

The [blue]Yen[/blue] and [blue]US Dollar[/blue] are the controlling columns, with Yen having priority in calculation if both are input. While calculation is pretty straight forward, US Dollar requires an additional conversion to Yen. There is a cell at L8 which holds the amount of Yen per $1.

The column which holds the formulas is [blue]YenTlt[/blue]. When [blue]Yen[/blue] is input the following truth table should be realized ...

[pre] G H I J
Yen US_Dollar Hrs Days Formula
----- --------- --- ---- -------
1 G
1 1 G*I
1 1 G*J
1 1 1 G*I*J
x x ""[/pre]
... and the statement that does exactly that is

[blue]=IF(G10<>"",IF(I10<>"",IF(J10<>"",G10*I10*J10,G10*I10),IF(J10<>"",G10*J10,G10)),"")[/blue]

The statement for [blue]US Dollar[/blue] input has an equivalent truth table:
[pre] G H I J
Yen US_Dollar Hrs Days Formula
----- --------- --- ---- -------
1 G*$L$8
1 1 G*$L$8*I
1 1 G*$L$8*J
1 1 1 G*$L$8*I*J
x x ""[/pre]
... and that statement looks like:

[blue]=IF(H10<>"",IF(I10<>"",IF(J10<>"",H10*$L$8*I10*J10,H10*$L$8*I10),IF(J10<>"",H10*$L$8*J10,H10*$L$8)),"")[/blue]

As I said earlier, both statements work fine independently. Its when I try to merge them together with the following that I have trouble:

[blue][blue]=If(G10<>"",YenStatement,If(H10<>"",DollarStatement,""))[/blue]

I realize the statements are a bit unwieldly ... if I could just only get them to merge! I tried using a VBA function but change events didn't seem to like it. Its as if the formula engine skipped right over it.

Any Ideas anyone?


[COLOR=darkgoldenrod][b]See Ya . . .[/b][/color]

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]

 

[SkipVought]

hi,

[tt]
=IF(G10<>"",MAX(G10*I10*J10,G10*I10,G10*J10,G10),MAX(H10*$L$8*I10*J10,H10*$L$8*I10,H10*$L$8*J10,H10*$L$8))
[/tt]

This ASSUMES that you'll have EITHER yen or dollars

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TheAceMan1]

SkipVought . . .

Nice ... Very Nice!

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[zelgar]

You probably need to adjust your formula. Currently if you work the 5 hours, it's equivalent to working 5 days. I would think that you would need to adjust your formula to account for the number of hours in a working day (e.g., if a working day was 8 hours

=IF(G10<>"",G10*MAX(I10/8+J10,I10/8,J10,1),H10*$L$8*MAX(I10/8+J10,I10/8,J10,1))

 

[TheAceMan1]

How are ya zelgar . . .

The people performing data entry are savy enough to perform the right data entry (and this is a simple quotation at its best!). as an example ... if someone stays at a hotel for 5nights, itcan only be input as five days ... or 5 hours ... a simple matter of getting the right multiplier! As long as the vakue turns out proper. If not ... there's a million reasons why this could never work ... Again ... it a simple quotation.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!