[Solved] A Few Tricks Needed...

[JoelP]

Hello Team,

I am seeking a little help on a few rows and columns in my worksheet attach.

Issue 1:

If I enter a time into cell AF24 and AW24 the total time shows up on cell BG25. This is what I need it to do, Now, If I don't enter any time into cell AF24 and AW24, how can I get it stop displaying a total of 0.00 in Cell BG25. In other words, if I don't enter a time in those two cells, I want it to display nothing in the box, meaning a blank box. Only display when times are enter.

Issue 2: In Cell A59 it reads Spareboard:, How can I have it display Spareboard only when I enter Times into cell AF24 and AW24? If nothing is entered, then it should be a blank box.

If you look at Cell A65, you will see that it reads FALSE because I don't have anything entered into Y24, Y26, Y28, Y30.

Please let me know if any of you can give me a hand on this matter.

Your help is greatly appreciated...

Thanks,

Joel...

[FJCC]

Try this in BH25

Code: Select all

=IF(OR(ISBLANK(AF24);ISBLANK(AW24));"";(IF(AW24-AF24<0;AW24+1;AW24)-AF24)*24)

The multiplication by 24 has to happen inside the IF() to prevent OpenOffice from converting the empty string "" to a zero. A similar use of an IF() should solve your problem in A59.

[JoelP]

Hello Team,

FJCC, your formula worked right on. I truly appreciate your help.

Now, in regards A59 I can not get it to work.

Any suggestion or can you do like you did and place your formula here please.

Also, I will like to get the Driver notations section to be blank if no data is collected from the different sections/cells.
Example:
If no calculation is entered from cells (BH25+BH27+BH29+BH31+BH33+BH35) then A59 should read blank
If no calculation is entered from cells (BH25+BH27+BH29+BH31+BH33+BH35) then H59 should read blank
If no calculation is entered from cells (BH25+BH27+BH29+BH31+BH33+BH35) then K59 should read blank, if there are calculations the it should read "X"
If no calculation is entered from cells (BH25+BH27+BH29+BH31+BH33+BH35) then L59 should read blank, if there are calculations the it should read "21.46"
If no calculation is entered from cells (BH25+BH27+BH29+BH31+BH33+BH35) then O59 should read blank, if there are calculations the it should read "="
If no calculation is entered from cells (BH25+BH27+BH29+BH31+BH33+BH35) then P59 should read blank, if there are calculations the it should read "H59*L59"

Your help will be greatly appreciated...

Thanks,

Joel...

[RusselB]

Before I give formulae, is it your intention that the same responses be given no matter what the entries in BH25 (etc.) are?
What should A59 and H59 be if there are calculations in the cells you specified?
Please note that with the returns as you have specified them, you will not be able to do any direct mathematics with the responses, as they will be text only.

[JoelP]

Hello RussellB,

In regards the Answers for BH25, etc, yes, the entries does not matter.

In Regards A59 and H59, I will like those two to be multiply with each other and the result be placed in P59. If that can't be done, then can we please make it be blank unless there is an entry in cell A59 & H59.

Any question, please let me know.

Your help is greatly appreciated.

Thanks,

Joel...

[RusselB]

I think we have a bit of a misunderstanding.
Is the information in BH25 (etc.) manually entered or calculated? If calculated, is there a number that it is impossible for the calculations to come to? Eg: 0 or a negative number
What should go into A59 and H59 when the entries in BH25 (etc.) are not blank (or not an impossible calculated amount, referencing my question above)?

[JoelP]

Hello RussellB,

The information in BH25, BH27, BH29, BH31, BH33, BH35 is automatically calculated.

What I need is...
When any of the following Cells BH25, BH27, BH29, BH31, BH33, BH35 are greater than zero, then A59 should read Spareboard.
When any of the following Cells BH25, BH27, BH29, BH31, BH33, BH35 are greater than zero, then K59 should read X.
When any of the following Cells BH25, BH27, BH29, BH31, BH33, BH35 are greater than zero, then L59 should read 21.46.
When any of the following Cells BH25, BH27, BH29, BH31, BH33, BH35 are greater than zero, then O59 should read =.
When any of the following Cells BH25, BH27, BH29, BH31, BH33, BH35 are greater than zero, then P59 should be the result of cell H59 multiply by cell L59.
If any of this cells are 0.00 or less, then the cell should display nothing, meaning blank.

Any question, please let me know.

Again, your help is greatly appreciated...

Thanks,

Joel...

[RusselB]

For A59 I'm going to suggest

Code: Select all

=if(and(BH25>0;BH27>0;BH29>0;BH31>0;BH33>0;BH35>0);"Spareboard";"")

You can copy that formula to the other cells K59, L59 and O59. Just change the entry "Spareboard" appropriately.
As to P59, just use

Code: Select all

=H59*L59

[JoelP]

Hello RussellB,

When I try the formula with =IF(and(BH25>0) ;"Spareboard";"") what this does is that when is more than one it makes the work Spareboard Dissapeared.
I am not sure what is causing this.

Now, The formula should make the cell blank if BH25 is Zero or Less. If its .25 or any number higher then it should read Spareboard.

Can you please let me know what is causing this confusion with the cell.

Your help is greatly appreciated...

Thanks,

Joel..

[keme]

The fact that "and" in your formula is in lowercase suggests that either you didn't copy your exact formula from the spreadsheet, or you are using a different language where "AND()" has a different name. Either way, when you only have a single compare, the AND() is not needed.

Code: Select all

=IF(BH25>0;"Spareboard";"") 

Edit: Oops. I see now that it is a copy from RussellB's suggestion. Missed that. Sorry!

[RusselB]

I only included the AND function in my formula as you stated that multiple cells needed to be checked in order for a single result.
If you only need one cell checked, then Keme's code is sufficient and non-redundant.

[JoelP]

Hello RussellB,

Please take a look at the New Sample form I've attach.

Cell BH 26 is the total for BH24 and BH25. However, when the cell BH24 and BH25 are blank, cell BH26 still shows 0.00, can I make this blank when cell BH24 and BH25 are blank?
If this can't be done, then can you help me with a formula that will calculate hours between AF24 & AW24 and between cells AF26 & AW26. It's like combining the two formulas below and place them all in just one cell which may make it all easier.

Formula in BH24: =IF(OR(ISBLANK(AF24);ISBLANK(AW24));"";(IF(AW24-AF24<0;AW24+1;AW24)-AF24)*24)
Formula in BH25: =IF(OR(ISBLANK(AF26);ISBLANK(AQ26));"";(IF(AQ26-AF26<0;AQ26+1;AQ26)-AF26)*24)
Formula in BH26: =SUM(BH24+BH25)

Also, please look at cell P52 and O54, the both should have "/" when cell AD55 have a total of above 0.00, however when the cell have nothing it's still displaying the forward slash which defeats the purpose of what I am trying to accomplish. When the cell have zero or blank it should display nothing. When the cell has a total of greater than 0.00 then it should display a forward slash.

Please let me know if you can make this happen for me.

Thanks,
Joel...

[RusselB]

For BH26 use

Code: Select all

=if(and(isnumber(BH24);isnumber(BH25));BH24+BH25;"")

I'll take a look at your attachment later, but for now there's an option for your BH26 problem

[RusselB]

I'm not sure why, but when doing the comparison for AD55, when putting it to "", it's still coming up with a value of greater than 0
Just how much greater, I have not been able to determine
I think the easiest solution is for you to change the formulae in P52 and O54 to

Code: Select all

=if(istext(AD55);"";"/")

Doing it this way will make it show the "" when the "" is showing in AD55, but the / when there's any number in AD55.

On a side note, I noticed that you are using the SUM function in a lot of places where you are specifying individual cell addresses, with the addresses being separated by the + character.
Doing this makes the usage of the SUM function redundant.
For example, in H65 you have

Code: Select all

=SUM(BH26+BH29+BH32+BH35+BH38+BH41)-(H71)

This could be done as

Code: Select all

=BH26+BH29+BH32+BH35+BH38+BH41-H71

or

Code: Select all

=SUM(BH26;BH29;BH32;BH35;BH38;BH41;-H71)

[JoelP]

Hello RussellB,

I try the new formula you gave me. The result does work, but when I don't enter a time in cells AF26 and AQ26 then it makes Cell BH25 Display nothing and that makes BH27 Have no result from BH24 and BH25. So it does create an issue.

Can you please let me know what fix you may suggest for this issue it creates.

Note: I am trying to get hours to add up in the spareboard section and to total the day at the end of the day.

Earlier I mention this formulas:
Formula in BH24: =IF(OR(ISBLANK(AF24);ISBLANK(AW24));"";(IF(AW24-AF24<0;AW24+1;AW24)-AF24)*24)
Formula in BH25: =IF(OR(ISBLANK(AF26);ISBLANK(AQ26));"";(IF(AQ26-AF26<0;AQ26+1;AQ26)-AF26)*24)
Formula in BH26: =SUM(BH24+BH25)

So I need all this formulas working together in order to give me the result I need. Now, I think if we get the times figure out as shown above plus add them together and place them all in the same cell would be much easier I think, instead of running 3 different cells if possible.

There would be times where an entry will not be made at all on cells
AF26 and AQ26 so this means that I still need a total for everything together. Right now, the way it is, it does not add AF26 and AQ26 to give me a total on BH26 if again, I don't make an entry as mention above.

Please let me know what other suggestions you may have with this issue...

Thanks,

Joel...

[RusselB]

Which formula are you referring to in your statement "I try the new formula you gave me"? I've given you a few formulae, and I have no idea as to which one you are specifically referring to.

Your own formula in BH25 requires entries in AF26 and AQ26. I have not changed that formula at all.

Rereading your last post, I think I know where the problem lies.
Please confirm or correct me on this, to the best of your ability.
I think, what you want, is for the accumulated time to show if there is any valid entry to give a time, even if there are other entries, before and/or after the valid entry, which return the empty cell.
If this is correct, then the problem lies in getting Calc to do the addition of the numeric cells, while treating the empty cells as 0.

[JoelP]

Hello RussellB,

Please see the attach file named Joel123.

I have highlight the cells and made all the explanations there.

Please let me know if this makes it more clear to you..

Thanks again for all the help...

Thanks,

Joel....

[keme]

=SUM(BH24;BH25) is the formula you need in BH26.

When you use the plus operator, Calc will expect both operands to be numeric. A formula that tries to do <number>+<text> will return an error.
When you use the SUM() function with entries separated by semicolon, it will sum all numbers given and disregard textual entries (but it will propagate any error entries).

If you want BH26 to appear blank when there is no entry, you can do this by formatting (do not show zero values).

You can also do it with a formula similar to yours, by using OR() instead of AND() and using SUM() instead of plus.
=IF(OR(ISNUMBER(BH24);ISNUMBER(BH25));SUM(BH24;BH25);"")

[JoelP]

Thanks so Much for all the help team...

Much Appreciated..