Hello everyone

I've uploaded an Excel file where I need some professional help.. Unfortunately in the german msofficeforums I couldn't receive an account verification email and couldn't contact an Administrator either so here I am.

What I need:

In the Excel file you can see "Total Rückerstattungen", These are the fields where the formulas belong. The formula must be able to calculate refunds. One refund is:

Being away from work over lunch, dinner and over night
Being away from work over dinner, over night and over lunch the next day
Being away from work over night and over lunch, dinner the next day

So you always have 3 possibilities, either you miss all 3 (row 24, 25, 26) on one day, or 26 the day before and 25, 26 the next day and so on.. I've included comments in the file itself so it's easier to understand.

The refund is always bound to being away over night + 2 Meals, so the formula itself should refer to the field under "night" (row 26)

If anybody could help me, I'd be really thankful as I have no idea where to start and what to do...



If you have questions please ask. I'm not sure if I explained good enough.

Attached Files

Vorlage Präsenzerefassung 2018.xlsx (283.3 KB, 14 views)

Into C27 enter the formula
and copy te formula to range C27:C31.

Other 1's (in rows 24:25) aren't essential, as by your setup the value1 in row 26 is always having mathing 2 values in rows 24:25 in same column, or in next one.

Thanks for the quick reply!

The other 1's are still essential because if C26 = 1, D24 = 1 but D25 = 0, there is no refund. Refund is only given if there's 2x 1's mated to the "away over night" 1's, sometimes there may be only one "1" mated to the "away over night" or maybe only one "1" in the "away over night", but None mated to it, you know what I mean?

That's the Problem I'm having.

Sorry I don't understand German - Difficult to help under these circumstances

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

So you may have some cells not filled jet? Then I'm afraid you are ot of luck!

Imagine a case, you have a cotinous row of dates with all 3 rows having 1 entered, and somewhere in middle cells in row 24:25 in some column are unchecked. You have go back to first column with 1 in row 26, and then check the full path back until the column with empty cells to decide, do those belong to this or previous column. Add one checked column more, and you have a step more to calculate.

Probably you have to add 2 more rows - 2 meals from current day, a night, and 2 meals from next day. And in column you can have 3 1's altogether.

How so? This is about a formula, you don't have to know what the texts mean

Yes, every month different cells are gonna be either blank or have a 1 in them.. It always belongs to the column where row 26 has a 1 in it. I'll try the more rows Thing but I'm not sure about it.

An example:
Now the question is, do belong these empty cells to DayB, or to DayC? No way to decide based only on DayB : DayC columns, without processing all days at least from start of block of 1's, when not from start of table. And what when the block starts with halfway filled data? P.e. in my example in column DayA the row 24 is empty, but really not filled jet.

Hello

The empty cells should belong to Day B

So I have 3 extra rows now with:

1) 2 Meals before night (easiest, no help needed with this one)
2) 1 Meal before and after night
3) 2 Meals after night

So it now just counts those 3 "possibilities" and I have them on paper now. What I now Need is some Kind of Formula that can calculate if there's only one refund or two in complicated cases like this one:

Row ... .......A......B......C.......D.......E ...
24 .............................1
25 ......................1......1
26 ......................1......1
...
30 ......................>Formula<


My Problem is this:

The example up there is one refund, right? Because B26, C24, C25 are one refund OR C24,C25,C26 could be one refund, but it's only one of either. The way I'm going now, it recognizes "B" and "C" as a refund, which I don't want.. So I would Need something like: If B30 is 1 due to C24 and C25 belonging to it, then don't put 1 in C30

Does something like this exist? I can upload the file again if you want.

Look at possible solution, which removes all uncertainty.

Attached Files

TableExample.xlsx (9.1 KB, 11 views)

That is true. This would be the easiest and least complicated way. I had the Job to get a formula to calculate based on those original 3 rows only but this will have to do.

I thank you my friend.

Btw., I implemented data validation on those 5 rows - so it isn't possible to mark 4 or 5 cells on same day.