Why is formula with error?
I would like to make logical formula rule in case 0 values in fields like
H1/H2
I try to do:
=IF(H1>0 *OR H1>0;H1/H2;0)
What is wrong with this logical formula.
Seems OR can not be included in logical rule? *OR
Is this correct?
The same sample issue:
=IF(F16=0;TODAY()-E16;F16-E16)
I like to do in this case AND operator inside formula:
=IF(F16=0 AND E16=0;TODAY()-E16;F16-E16)
Last edited by toplisek; 01-09-2010 at 09:11 AM.
Do you mean perhaps
=IF(H2<>0;H1/H2;0)
ie avoid #DIV/0! error... is that the intention ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Re: Why is formula with error?
Yes, I like to do this
What does it mean in words <>0
I try to do:
=IF(H12<>0;H12/H11;0)*OR (IF(H11<>0;H12/H11;0))
Why it does not work with value 0 if there is 0 in H12 OR H11?
I have another sample to measure delay of payments:
=IF(F16=0;0)*OR (IF(E16=0;0))*OR (IF(F16>0;TODAY()-E16;F16-E16))*OR (IF(E16>0;TODAY()-E16;F16-E16))
Why this does not work and with error in formula?
Last edited by toplisek; 01-09-2010 at 09:32 AM.
<> : does not equal
In the calculation H12/H11 ... it does not matter if H12 is 0 - it is the divisor that is key - ie H11.
Examples:
=0/1 -> 0
=1/0 -> #DIV/0!
I can't really make a great deal of your other formula... in basic terms
Please Login or Register to view this content.
=IF(F16=0 AND E16=0;TODAY()-E16;F16-E16)
not sure what this is supposed to do either!
you use AND this way
and(f16=0;e16=0) but your formula even if rewritten doesnt make sense
=IF(and(F16=0 ; E16=0);TODAY()-E16;F16-E16)
today()-0 is still = today()
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Great. I tested and works DonkeyOte with =IF(OR(H11<>0);H12/H11;0)Originally Posted by ;2229565
<> : does not equal
In the calculation H12/H11 ... it does not matter if H12 is 0 - it is the divisor that is key - ie H11.
Examples:
=0/1 -> 0
=1/0 -> #DIV/0!
I can't really make a great deal of your other formula... in basic terms
Please Login or Register to view this content.
Thanks
To recap, you don't need the OR as you have only 1 test, so:
=IF(H11<>0;H12/H11;0)
You would only use OR/AND etc if you had more than 1 test
Thanks again great.
This works perfect as calculation of delay in payment:
=IF(AND(F16<>0; E16<>0);TODAY()-E16;F16-E16)
you see main point is that in case payment and received payment date is 0 in databse it will just put 0 not value difference. But if there is either date it will put difference...
Question is just how to add additional OR/AND in case of =IF(AND(F16<>0; E16<>0);TODAY()-E16;F16-E16)
Re: Calculation of delay in payment
I have issue with simple formula but do not know why it shows 0 even in the case of F16 more than 0?
Try dates like:
E16 as Start date Nov 03 2009
F16 as End date Nov 09 2009
=IF(AND(F16>0;E16>0);F16-E16)*OR(IF(AND(F16=0;E16=0);0))*OR(IF(AND(F16=0;E16>0);TODAY()-E16))
Separate formula works with exact numbers but OR does not.
Possibilities are the following:
1. both more than 0 and calculation F16-E16
2. both equal 0 and calculation with value 0
3. if F16 is 0 it should calculate delay of payment: TODAY()-E16
Is there issue in calculation as formula does not have an error?
=if(and(f16>0;e16>0);f16-e16;if(and(f16=0;e16=0);0;if(and(f16=0;e16>0);today()-e16)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks