Create a conditional formula that results in another calculation

[dyrret]

I'm just learning to use Excel.

I want to enter data in either column A (litres) OR column B (US gallons).
Column C will convert to imperial gallons.

I would like to create a formula in column C that would result in this logic:

IF there is data in cell "A(n)" then do calculation A(n)/4.54, if no data
in A(n) and there is data in cell "B(n)" then do calculation B(n)*.83

 

[T. Valko]

Try this:

=IF(COUNT(A2),A2/4.54,IF(COUNT(B2),B2*0.83,""))

 

[dyrret]

Thankyou,
Works wonderfully and makes sense when I see it. I was trying to construct
something too complicated....missed the more straight forward answer.
dyrret

 

[dyrret]

One question re the answer: I understand what is being "said" except the
double quotation marks.

 

[David Biddulph]

That's an empty string, so if you have no data in A or B your result will be
blank instead of zero or the boolean FALSE.

 

[T. Valko]

If *neither* cell contains a number then the formula returns a blank. The ""
is the syntax for "blank". If you'd rather have a 0 returned:

=IF(COUNT(A2),A2/4.54,IF(COUNT(B2),B2*0.83,0))

Or, you could return an informational message like "no value entered":

=IF(COUNT(A2),A2/4.54,IF(COUNT(B2),B2*0.83,"no value entered"))

Using the COUNT function ensures that a number has been entered in one or
the other cells. It prevents errors if someone accidentally enters a text
string or if one of the cells already contains an error. It may not be
necessary but it makes things robust and doesn't add undue complexity to the
formula.

 

[dyrret]

Thanks again.

If *neither* cell contains a number then the formula returns a blank. The ""
is the syntax for "blank". If you'd rather have a 0 returned:

=IF(COUNT(A2),A2/4.54,IF(COUNT(B2),B2*0.83,0))

Or, you could return an informational message like "no value entered":

=IF(COUNT(A2),A2/4.54,IF(COUNT(B2),B2*0.83,"no value entered"))

Using the COUNT function ensures that a number has been entered in one or
the other cells. It prevents errors if someone accidentally enters a text
string or if one of the cells already contains an error. It may not be
necessary but it makes things robust and doesn't add undue complexity to the
formula.

 

[dyrret]

Thank you.....seeing examples and getting explanations is helping me learn.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks again.

 

[JC-PS]

You're welcome!

--
Biff
Microsoft Excel MVP





Hello T. Valko.

I just went through the formula on the discussion of 08-31-08 responding to
dyrret asking:
"I'm just learning to use Excel.
So do I. I'm learning use Excel WorkSheet. But instead of a column as
mentioned before, I need a single cell like
AB shows on decimal equivalent of minutes or hour with minutes, example,

AC -- 15 minutes = mathematical formula would be, 15 x .01666667 = .25--->>>
this decimal equivalent

of 15 min
AC -- 1hour 35minutes (1.35) = 1.583 this results on AB, and minutes on AC.
I tried modified the one suggested here before but failed.

Any help will be significant.

 

[JC-PS]

I just went through the formula on the discussion of 08-31-08 responding to
dyrret asking:
"I'm just learning to use Excel.

So do I. I'm learning use Excel WorkSheet. But instead of a column as
mentioned before, I need a single cell like
AB shows on decimal equivalent of minutes or hour with minutes, example,

AC -- 15 minutes = mathematical formula would be, 15 x .01666667 = .25--->>>
this decimal equivalent

of 15 min
AC -- 1hour 35minutes (1.35) = 1.583 this results on AB, and minutes on AC.
I tried modified the one suggested here before but failed.

Any help will be significant.