Formula Results as False when True

[tjb1122]

Hello,

I have created a spreadsheet to help me calculate an agents non-productive time. In this spreadsheet I have a few formulae (formulas?) that reference each other. My problem, is that some of these formulae (as?) come up showing that something is false, when it is in fact true. IE N-O=0 it should read "Good" if this is true, but instead it reads as False and moves on to the next part of the formula. The same formula on a different row works exactly as expected. The only difference I can tell is that the formula that doesn't work as intended references another formula that uses a different part of the IF statement. Examples below:

Working Formulas (ae?)

Please Login or Register  to view this content.

Non-Working Formulas (ae?)

Please Login or Register  to view this content.

I know this may not be the cleanest way to do this (or it may be, I'm not sure because I'm not a complete expert on excel functions, I just know enough to get by), but if you could help me understand why it's reading as false, that would be very helpful! Thank you! (worksheet should be attached)

[AliGW]

Welcome to the forum.

Where shall we find these malfunctioning formulae?

[Glenn Kennedy]

No. There are no formulae anywhere in you tiny sample.

FOUND THEM!! Now to see if I can understand wha you want!!

[tjb1122]

I'm sorry, the first formula is found in Column N and the second formula is found in Column R.

[AliGW]

I don't see anything returning FALSE.

[Glenn Kennedy]

Amend M3 to:
=ROUND(1440*IFERROR(IF(C3="","",(MOD(C3-B3,1)*24-D3)),IF(C3="","",(MOD(C3-B3,1)*24))),0)/1440

Amend N3 to:
=ROUND(1440*SUM(E3,G3:M3),0)/1440

[tjb1122]

It's returning FALSE within the formula (it's an IFS statement) it's saying that N3-03 does not equal Zero when it in fact does and should return the value "GOOD". Then it goes on to the next statement of if N3-O3<>0 then N3-03 and the result comes up as 0.00 which is correct, but not the result I was looking for.

[tjb1122]

I'm sorry, I'm not wanting the result you're formula would provide. Perhaps my previous response will clear up what I'm looking for.

[AliGW]

So what ARE the results you are looking for?

[Glenn Kennedy]

=IFS(F3="Coordinator","",F3="Escalation Agent",N3*0.6,F3="Training","",N3-O3=0,"GOOD",N3-O3<>0,N3-O3)

Red - when subtraction =0. Blue when it isn't. It is zero, so it gicved GOOD. Whats wrong woith that?

What answers do ypu expect in each of the 3 result cells?

And when replying, indicate WHO you are talking to.

[tjb1122]

The attached screenshot shows the 4th Logical Test shows a result of "False" when it should absolutely be "True" because N3003 equals 0 (they're both 5.00)

I hope this helps, because that's the only way I know to explain what I'm looking for.

[tjb1122]

It wouldn't let me attach a screenshot...

[tjb1122]

Glenn

I need R3 to return the value "GOOD", the other two were examples of working formulae.

[Glenn Kennedy]

Did you ACTUALLY amend the formulae as suggested? I don't just make these things up. I test them.

[tjb1122]

=IFS(F3="Coordinator","",F3="Escalation Agent",N3*0.6,F3="Training","",N3-O3=0,"GOOD",N3-O3<>0,N3-O3)

Red - when subtraction =0. Blue when it isn't. It is zero, so it gicved GOOD. Whats wrong woith that?

What answers do ypu expect in each of the 3 result cells?

And when replying, indicate WHO you are talking to.

The problem is, it didn't give "Good". It gave 0.00

[tjb1122]

Okay, I pulled up your file and it looks like it's working... I don't understand why mine did not. I noticed you only changed N and O to make it work, but logically it should have worked without the rounding/multiplying and dividing.

[Glenn Kennedy]

R3 QUITE CLEARLY shows good on the file that I posted.

[Glenn Kennedy]

Read post 6, for Goodness sake. The problem was NOT in that cell, but in the cells that I suggested that you amend. If you are not prepared to read and act on what is being said to you....

[tjb1122]

Ugh... My apologies, for upsetting you. You obviously are having a tempermental day and I would prefer to speak with somebody who can help me understand what the problem WAS so I don't need to continue upsetting you.

[Glenn Kennedy]

Season's greetings to you too. Good luck & goodbye.

[tjb1122]

Ali,

Can you please, with patience, help me understand why my formulae didn't work? It should have, and yet it took Rounding/Multiplying and dividing Column N and O. My goal is to not need to ask this question again, and unfortunately Glenn was not conducive to that goal.

[AliGW]

It won't work without the rounding. That's all you need to know.There's something called a 'floating point error' (Google it) that sometimes causes these apparent anomalies.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Glenn Kennedy]

Thanks for the negative rep, BtW.

[tjb1122]

Thanks for the negative rep, BtW.

Please, when you're of a mindset that isn't so negative (and quarrelsome)... read back through this thread and try to take it from the perspective of the person you, for all intents and purposes, called stupid. Then think: "Would I give a positive rep for the way I was treated?" We can be finished now... Thank you Ali for your assistance, Thank you Glenn for your work in updating the code, (though your delivery wasn't the best). Have a great day!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.