#1
|
|||
|
|||
Formula needed; I'm helpless
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. |
#2
|
|||
|
|||
Into C27 enter the formula
Code:
=IF(C$26=1;1;"") 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. |
#3
|
|||
|
|||
Quote:
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. |
#4
|
||||
|
||||
Sorry I don't understand German - Difficult to help under these circumstances
__________________
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
Quote:
Quote:
|
#7
|
|||
|
|||
An example: Code:
Row ... DayA DayB DayC DayD DayE ... 24 ... 1 1 1 1 25 ... 1 1 1 1 26 ... 1 1 1 1 1 |
#8
|
|||
|
|||
Quote:
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. |
#9
|
|||
|
|||
Look at possible solution, which removes all uncertainty.
|
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
Btw., I implemented data validation on those 5 rows - so it isn't possible to mark 4 or 5 cells on same day.
|
Tags |
complicated, formula, needed |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help needed with IF formula with a Vlookup | jonnie_rc | Excel | 6 | 08-31-2017 01:27 PM |
hard formula needed | nfsmith | Excel | 6 | 03-19-2016 06:12 PM |
Formula help needed | Cosmo | Excel | 1 | 08-23-2012 11:50 AM |
Very simple formula needed! | nicholes | Excel | 4 | 04-12-2012 08:28 AM |
Excel formula needed. | Rod | Excel | 1 | 08-03-2009 06:55 AM |