complex IF statement

[Manster]

Hi...


How can I create a function/formula in this case:


If either values of 35.54 or 45.54 show up, it should be subtract to 0.94.


"=If(or(35.54,45.54),35.54-0.94,35.54-0.94)"

I realize that this function is incorrect,so how can I create the right function which follow the sentence mentioned?


Hope you all can help me

 

[NARAYANK991]

Hi ,


When you say "show up" , do you mean that some cell gets this value ? If I assume that , say , the cell G7 gets this value ; suppose I also assume that in case G7 gets this value , this formula will be in cell B13 ; then B13 will have this formula :


=IF(OR(G7=35.54,G7=45.54),G7-0.94)


This by itself will do the following :


1. If G7 = 35.54 , then B13 will have the value 35.54 - 0.94 = 34.6


2. If G7 = 45.54 , then B13 will have the value 45.54 - 0.94 = 44.6


3. If G7 has any other value , then B7 will have the value FALSE ; this is because the IF statement has not specified what should happen if the OR function returns a FALSE value i.e. the IF statement does not have a third parameter.


Narayan

 

[Manster]

Hi Narayan!


you help me to solve my problem. Thanks alot!!

 

[Manster]

Hi everyone..


Problem 1 know already. Then, 2nd problem coming..


I had tested in OR statement,its logical numbers limit if I not mistaken only 44 logical expression. Next logical the expression seems like ignore/no function. My logical expression is so so many at my office task...start from 0.54,1.54,2.54........1219.54


So, how can I include all these numbers into logical expression if it limit only 44 expression?

This is my example statement.


=IF(OR($B$5=J1,$B$5=J2,$B$5=J3),B5-D6,B5)


J1 to J100: 0.98,1.98......100.98


If there is any suggestion about changing the simple better statement or whatever is good for me to tackle this problem.


Thanks & Regards

 

[Hui]

Manster


Firstly, Please start a new Form Question for new Questions, it makes managment and subsequent searches easier


Can you give us some more details of what your trying to do


I suspect that doing something like

=If(Right(Text(cell ref, "#.00"),2)=98, Do something, Do something else)

may be more applicable

 

[NARAYANK991]

Hi ,


Let us assume the following :


1. You have a list of values 0.98 , 1.98 , 2.98 ,..., 100.98 in the range J1:J101.


2. You have a constant value in D6 , say 0.94.


3. You have a variable value in B5 , which can be any value from within the list J1:J101 or any other value too.


4. Your formula is in say G5 :


=IF(NOT(ISERROR(MATCH(B5,$J$1:$J$101,0))),B5-D6,B5)


Narayan

 

[Manster]

Hi Hui,


Your equation was nice simple.I'm interested to know. However, when I tried it,its the problem.Maybe I don't know much this function doing.


=IF(RIGHT(TEXT(B9, "#.98"),2),B9-D6)


Variable Value

B9:1.98


Constant Value

D6:0.98


A9: Result with formula


For instance, when I enter the value in column Variable value(let say B9) and that value is any values.


Then, A9 which has the formula will detect that B9 has #.98 value and it will subtract 0.98, and the balance of the value remain in B10 for example. My problem is I have many many fixed values which I mentioned(0.98 to 1219.98),so when I use OR function, it has the limit of the logicals. Therefore, it come trouble for me to key in many kind of fixed value that I have.


Hopefully you understand..Thanks alot guys!!

 

[Manster]

Hi Narayan,


I tried your statement..however seems like not follow the execute when I key in start from 50.98 up in B5. Here attached file for your further details.


http://www.4shared.com/file/p2H8kB4H/MANUAL_2.html

 

[Hui]

You left out the =98 part

try this

=IF(RIGHT(TEXT(B9, "#.98"),2)="98",B9-D6,"Not .98")

 

[NARAYANK991]

Hi ,


The problem was the values in column G were not numeric.


I entered 0.98 in G5 , 1.98 in G6 , and dragged down till G165.


The formula gave the expected result.


Narayan