IF true THIS, IF false leave as it is..

  • Thread starter Thread starter poonam
  • Start date Start date
P

poonam

I have used IF function this way.

A cell contains countries.

Country Minutes
A B
1 USA 35689



USA 35689
CANADA 26789
AUS 1487
OTHERS 2498


In A1 if i change from USA to Canada, Canda's minutes are
displayed. In cell B2 i have IF function =if(A1="USA",B1,
LEAVE as it is.

Value if FALSE should leave the figure as it is. Using ""
gives me blank cell. What should i enter in Value if FALSE
to leave the figure as it is?
 
Poonam,

The "value if false" should be another IF statement, and
so on for each country. This is OK if the list is short,
but you might be better served with a VLOOKUP instead.

Joe V
 
You're creating a circular reference, so you need to limit XL's
calculation of the formula. Choose Tools/Options/Calculate and check the
Iterations checkbox. Enter 1 for the number of iterations.

Then in B2 enter

=IF(A1="USA",B1,B2)
 
OP wasn't asking for both values and formulas in once cell. S/he wanted
the formula in B2.
 
Maybe:

In B1 enter:

=IF(ISNUMBER(MATCH(A1,{"AUS";"CANADA";"USA"},0)),LOOKUP(A1,{"CANADA",26789;"
USA",35689}),2498)

A1 is assumed to house a country.
 
wow,wow! worked!
Thanks!!!
-----Original Message-----
You're creating a circular reference, so you need to limit XL's
calculation of the formula. Choose
Click to expand...
Tools/Options/Calculate and check the
 
POONAM

I'm sure there is allways a better solution than creating
a circular reference, you really shouldn't mess with
circular references unless you are absolutelly sure you
know what it does... perhaps a vlookup, or a double if
statement but be carefull with CR's...

Cheers
Juan
 
Juan Sanchez said:
I'm sure there is allways a better solution than creating
a circular reference,
Why?

you really shouldn't mess with circular references unless you are
absolutelly sure you know what it does...
Click to expand...

It's not particularly complicated... I don't think I've ever seen a
problem with a production worksheet that used intentional CRs.

IMO, there are two big problems with CRs. The first is during design,
when checking the iterations box removes the warning if a CR is entered.
As long as reasonable care that there are no inadvertent CRs entered,
the danger is nil.

The second problem is maintenance. Coming back to a sheet with CRs six
months from now may be confusing. However, that's often the case with
models that don't use CRs.

One area that I almost never recommend CRs is with accumulators. That's
primarily because clearing an accumulator can be difficult for many
users. Of course, I hardly ever recommend accumulators at all, since
they allow no history to troubleshoot if problems occur.
perhaps a vlookup, or a
double if statement but be carefull with CR's...
Click to expand...

How would a "double if" statement replace a CR in this case?

In this situation, when there are four options, only one of which should
cause a change in the cell with the formula, I'm at a loss to see how
VLOOKUP would help either...
 
Just use a vlookup man, its the easiest thing in the world

Create a lookup range on another sheet and just use that

New sheet would contain something like this starting in Cell A1

CountryID CountryName
1 USAA
2 Mexico
3 France
4 Italy
5 Oz


Now on your other sheet have the lookup

=Vlookup(a1,"Other sheet name"!a1:b6,2,0)

This should bring back the country name based on the value in A1, hop
that helps..
 
Except that the OP's problem statement said that B2 should only change
if A1 is "USA". Otherwise it should stay the same.

VLOOKUP, whether the easiest thing in the world or not, won't work for
that situation.
 
After reviewing the OP, I agree, double if would not work,
still, if B2 is ment to have only the value of USA why
double the formulae, he could use Vlookup so that the
value of the USA minutes is extracted directly from the
list to B2.

now, question, If you where designing a report and in the
process you find that a formula can be either a CR or not,
and get the same result, would you use the CR?

I wouldn't, but then again, here I post questions and
answers (to the easy questions), you on the other hand
seem to post only answers, so I'm sure your knowledge is
way, way better than mine.

I don't like CR's because it always represent a doubt on
the report for me... maybe I'll look into it and find a
place to use them, safelly... :)

Cheers
Juan
 
Comments in-line.

Juan Sanchez said:
After reviewing the OP, I agree, double if would not work,
still, if B2 is ment to have only the value of USA why
double the formulae, he could use Vlookup so that the
value of the USA minutes is extracted directly from the
list to B2.
Click to expand...

Perhaps the OP want's to enter data in only one place (e.g, A1:B1), and
doesn't want to store USA data separately.
now, question, If you where designing a report and in the
process you find that a formula can be either a CR or not,
and get the same result, would you use the CR?
Click to expand...

I almost never use CRs, but the usage depends largely on client
requirements. I've rarely found that a situation appropriate for CRs
arises where regular functions will work. Likewise, most of the time,
tables or lists are more appropriate than CRs.

The usual dichotomy is between using CRs and Event macros. I prefer
Event macros, because I can do a lot more validation and troubleshooting
internal to the macro than I can using CRs. However, some of my clients
by policy turn macros off, so CRs are the only option.
I don't like CR's because it always represent a doubt on
the report for me... maybe I'll look into it and find a
place to use them, safelly... :)
Click to expand...

As I said, there are dangers to using CRs - both in design and in
maintenance. Those problems, however, are not insurmountable.
 
Back
Top