Shorten my IF formula for work schedule

Discuss the spreadsheet application
Post Reply
thrivepetfood
Posts: 1
Joined: Mon Oct 28, 2019 7:31 pm

Shorten my IF formula for work schedule

Post by thrivepetfood »

First off, I know this schedule is unconventional, but my boss didn't understand formulas and did all the calculations manually so it was a disaster. I've been in charge of it now and have figured out a formula.

Second, I want to keep it this way and simply find a way to convert the formula into OpenOffice. It works just fine with excel and converting it has been challenging for me. I'm ok with formulas, but I'm not the greatest, it took me a long time to get what I have now.

Third: I do not want to split the formula up. I would like to keep it to one cell.

Any tips on converting would be awesome. I'm very new to open office and nothing I searched has turned up results.

So this is the formula:
IF($E$39="Employee4";IF(($G$39-$F$39)>=(6/24);($G$39-$F$39)-(1/48);($G$39-$F$39));0))*24

However, it is obviously going from A to G, so there's a total of 6000+ characters.
Attachments
This is a template of the schedule.
This is a template of the schedule.
OpenOffice 4.1.6 Windows 10, 64bit
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Shorten my IF formula for work schedule

Post by keme »

The only advice I have to offer at this time is: Please consider having your data in vertical tables, and use extraction to produce the quick lookup tableau. That makes summaries much easier to create.

Depending on the task at hand, the use of vertical tables may not be applicable.

Otherwise, for the particular situation you presented ...
thrivepetfood wrote:... However, it is obviously going from A to G, so there's a total of 6000+ characters.
Yes everything in that schedule is obvious ... to you.

We don't read minds.

Also, we don't enjoy typing data from reading a screenshot image into a presumed copy of your spreadsheet, which we would need to in order to test our suggestions before submitting them.

Where your formula belongs (within the displayed grid, or somewhere else) is not apparent. We might be able to deduce what you try to do from content displayed.

Image doesn't show row/column headers (coordinates). We may guess that your screenshot runs from row 1 downwards, and starting at column A, and there are no hidden rows columns. We do not know this, but it is a fair guess.

Trying to divine a solution out of this amount of required typing and guesswork is not very tempting.

If you can upload a copy of your file instead, you stand a better chance of getting help. No data retyping required from us. If you can also explain what you are trying to do, instead of only the formula which says how you would do it, we might understand how to combine multiple versions of your formula or otherwise filter or aggregate data to come up with what you need.

No guesswork. No divination or other magic required. Everybody wins.
Post Reply