• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Request for IF formula

P

Pathik Shah

New Member
Hello,

I would like to apply formula in excel for my temp. reading to know that weather it is in range or not.

Example :-

Readings:

10 PM : 10 C
12 PM : 8 C
1 AM : 15 C
4 AM: 16 C

Acceptance Range : 10 C to 15 C

Less than 10 C is " Not Acceptable" and More than 15 C also not acceptable.

Please let me know how I can make formula to use the same.

Thanks in advance.

Regards...Pathik Shah
 
Hi Pathik,

Welcome to Chandoo.org forums.

Is the posted data (e.g. 10 PM : 10 C) in one cell or separate cells?

If it is in separate cells then how is cell containing 10 C formatted, a number or manual entry of 10 C which is text?

Do you want to show result for each cell or one result for all of the above data?
 
shrivallabha said:
Hi Pathik,

Welcome to Chandoo.org forums.

Is the posted data (e.g. 10 PM : 10 C) in one cell or separate cells?

If it is in separate cells then how is cell containing 10 C formatted, a number or manual entry of 10 C which is text?

Do you want to show result for each cell or one result for all of the above data?
Click to expand...

Hello Shrivallabha,

Both values are in different cells. 10 C is the manual entry in number format. It is entered as 10 only. I want to show report for that perticular cell only.

Thanks for reply.
 
Let's say temperature 10 is entered in cell B2 then the formula should be:
=IF(AND(B2>=10,B2<=15),"Acceptable","Not Acceptable")
And then copy down.
 
Hi,

In the formula, B1=IF(A1=30,B1=5500,B1=5500/30*A1), I either get TRUE of FALSE or just plain zero, 0.

What's wrong with it

Please guide

Regards
Onil Gandhi
 
Hi Onil ,

What you have indicated is not really a formula , since in Excel all formulae start with the = character. The following :

=IF(A1=30,B1=5500,B1=5500/30*A1)

is a valid formula , though it may not do what you think it should be doing.

If you see the Excel help on the IF statement , things will become clear.

If you can specify what you want to do in plain English , we can walk through the appropriate formula which will do that.

Narayan
 
Hi Narayan,

I want from the number value of cell B1 be equal to 5500 (if the number in A1 is equal to 30) or be equal 5500/30*A1 (if the number in A1 is less than 30). Thereafter I want to use the value of B1 for further calculations.

Reagrds
Onil
 
Great! it works, but the query still stands...

What is intrinsically wrong with =IF(A1=30,B1=5500,B1=5500/30*A1)

Also now I want to take it to the next two levels....

1. Either the cell A1 does not accept a number greater than the number of days in a month, OR in B1 instead of "This result is to be decided" it shows, based on the no of days in a month "No of days are incorrect"

2. The cell A1 does not accept a number greater than the number of working days in a month ie number of days minus no of Sundays

Please guide
 
Last edited:
Hi Onil ,

The Excel help should explain everything. Let me try.

The syntax of the IF statement is :

=IF( check a condition , output a result if the check is TRUE , output a result if the check is FALSE )

check a condition :

This is to be framed as just a cell reference , or an expression which can be evaluated.

A cell reference would be for example J17 , as in :

=IF( J17 , 10 , 5 )

Try this formula with various values in J17 ; enter values such as :

a. negative values
b. 0
c. positive values
d. text values

An expression would be :

=IF( J17 >= 13 , "Teenager" , "Child" )

Again try this formula with the different values indicated above.

The expression can be quite complicated , and include other Excel functions , including another IF statement !

output a result if the check is TRUE :

This can be a literal value , or a cell reference , or an expression which can be evaluated.

If you enter an expression such as 5500/30*A1 , Excel evaluates it using the contents of cell A1 , and returns an arithmetic result.

The same happens with your initial formula , where this expression was :

B1 = 5500/30*A1

But the evaluation in our expectation is different from the evaluation as carried out by Excel !

As far as Excel is concerned , this is an equation ; to evaluate it , the left hand side and the right hand side are evaluated separately , and then compared to see whether they are equal.

If this formula is entered in any cell other than A1 and B1 , there is no problem ; Excel will see what cell B1 contains . what cell A1 contains , evaluate the two sides separately , compare them , and then return a TRUE or FALSE result ; a TRUE result would mean that both sides are equal , a FALSE result would mean that they are unequal.

If this formula is entered in B1 itself , then you are creating what is called a circular reference , and Excel will display a message to this effect. You will need to correct either the formula or the Excel options which will then permit such a formula to be entered and evaluated correctly.

output a result if the check is FALSE :

The above description applies to this section also , except that this section is executed only when the condition is FALSE.

Narayan
 
Hi,

Thanks for the detailed reply.

I recommend that you try in cell B1 : =IF(A1=30,B1=5500,B1=5500/30*A1)

first it gives a circular reference warning, then, after ignoring it, no matter what cell A1 contains, cell B1 gives the output 0

But this works in cell B1 =IF(A1=30,"B1=5500","B1=5500/30*A1")
and there are no circular warnings.

Here for every value of A1 not equal to 30, cell B1 shows B1=5500/30*A1 and only when A1 = 30, cell B1 shows B1=5500.

The query therefore still stands, why doesn't in cell B1 : =IF(A1=30,B1=5500,B1=5500/30*A1) work?
 
Hi ,

I suggest you think over the two expressions to understand what Excel is doing in evaluating them.

In your first formula , which does not generate any error :

=IF(A1=30,"B1=5500","B1=5500/30*A1")

the expression :

"B1=5500"

is literal text , because it has been enclosed in quotes ; there is no evaluation involved , since Excel treats the B1 inside the quotes as just text , and not the contents of cell B1.

The same reasoning can be applied to the expression :

"B1=5500/30*A1"

where the contents of neither cell B1 nor cell A1 play any role.

Your other formula , which results when you remove the quotes does involve looking at the cell contents.

If you read up on Circular Reference you should get an idea of what situations generate this error , and what are the resolutions available.

Narayan
 
You must log in or register to reply here.
Back
Top