• 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.

EXCEL FORMULA

S

sanjay

New Member
=IF(AND(AY$6<>"G.I. SHEET",(OR(AY$4="IMPORT",AY$4="LOCAL"))),(AY$13+80),(OR(AY$13+110,0)))

this formula should give me a zero value but its giving me "TRUE"

CAN SOMEBODY HELP ME PLS.
 
Sorry, but I'm going to tell you how I'd do it, rather than do it for you.


You have a compound formula. Some part of it is not working correctly. You need to simplify it to find the part that is not working properly.


First, your formula is going to give you one of two answers:

(ay$13+80)

(or(ay$13+110,0)


So, I suggest you put these two formulas by themselves into a cell:

=ay$13+80

=or(ay$13+110,0)


Do that and see if either of them evaluates to "true."


If so, you're probably already half-way to your answer.


don
 
Hi Sanjay ,


In your formula , the IF condition results in one of the following two outcomes happening :


1. AY$13 + 80 , which is a arithmetic one


2. OR(AY$13+113,0) which is a logical one


What is the significance of this second result ? What do you want to do ?


Narayan
 
THE VALUE SHOULD GIVE ME AY13+80, OTHERWISE IT SHOULD GIVE ME AY+110 OR ZERO..

2ND RESULT HAS 2 OPTION EITHER IT GIVES ME AY+110 OR IT WILL GIVE ME ZERO.
 
Hi Sanjay ,


When you say the second outcome is either AY13+110 or 0 , that means you need another IF statement nested within the first , to decide between these two outcomes.


Narayan
 
Sanjay,


An OR statement evaluates to "true" or "false"


Did you put the two statements into cells by themselves like I suggested, or do you just want to argue about what might happen without any testing or proof?


Put =AY$13+80 into a cell. What does the cell display?


Now, put =OR(AY$13+113,0) into a cell. What does the cell display?


Don
 
Hi Sanjay,


what is the Objective of this formula, will u explain the criteria for selection of AY13+80, AY13+110 and 0??


Narayyan is right that you should add another if it should go like this. The last OR(AY13+110,0) should be replaced with:


...IF(xyz=abc,AY13+1100,0)


but what is xyz and abc is my earlier question for it will decide which one of the three must be selected!!


Regards,

Faseeh
 
Can u send me a test mail pls.. on my yahoo... am still not getting it.. maybe it will help if il send my worksheet so u can take a look at it.. karena_narvaez@yahoo.com.


Thank you very much.
 
Hi sanjay,


Just drop an email at faseeh10@hotmail.com or upload file on some file sharing site it will fixed as soon as possible.


Regards

Faseeh
 
Hi, sanjay!

Just guessing...

=SI(AY$6<>"G.I. SHEET";SI(O(AY$4="IMPORT";AY$4="LOCAL");AY$13+80;AY$13+110);0) -----> in english: =IF(AY$6<>"G.I. SHEET",IF(OR(AY$4="IMPORT",AY$4="LOCAL"),AY$13+80,AY$13+110),0)

Regards!
 
ok.. thanks a lot guys... but til now im not getting the correct one.. i have another option:

in 1ST CELL say C11 i have ds formula =IF(AND(C$4="LOCAL",C$6="G.I. SHEET"),(C$13+80),0)

now, on 2ND CELL, IF ON 1ST CELL THE RESULT IS 0, THEN =IF(OR(C$4="IMPORT",C$4="LOCAL"),(C$13+80),(C$13+110))OTHERWISE IT SHOULD BE 0. wat formula i should use for "if C11=0 then

=IF(OR(C$4="IMPORT",C$4="LOCAL"),(C$13+80),(C$13+110)) otherwise 0.
 
Sanjay, sorry for disturbing once again , u just want to execute the mentioned formula if col40 is showing zero??? Thats it??
 
...... Well it is not Column 40 and 116, it is Row 40 n 116!


Formula should be:


If(C40=0,"",YourMrntionedFormula)


This will show a blank if C40 is zero, else wise execute the formula in with ur set conditions. Hope it will work.


Regards,

Faseeh
 
Ok Use....


If(c40=0,UrFormula,"")


In place of UrFormula paste one that u mentioned in ur email.
 
=IF(C$40=0,IF(OR(C$4="IMPORT",C$4="LOCAL"),(C$13+80),(C$13+110)),0)


ok.. i think i got it... waaaaaahhh...

tnx a lot...:)
 
@ Sanjay, Normally people use "Wow....." I read for the first time "Waaaaahhhhh", I think I can trace back the word to some Urdu-Hindi-Arabic origin and is very commonly used in Asian Sub-Continent. By the Way I am really getting off-topic. Glad to help you. :)


Faseeh
 
@SirJB7


Hi, me!


Am I wrong or this sanjay's formula:

=IF(C$40=0,IF(OR(C$4="IMPORT",C$4="LOCAL"),(C$13+80),(C$13+110)),0)


looks a lot like this posted before -11 posts earlier- (and guessed don't ask me how)?

=IF(AY$6<>"G.I. SHEET",IF(OR(AY$4="IMPORT",AY$4="LOCAL"),AY$13+80,AY$13+110),0)


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