Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-07-2017, 04:27 AM
Ylli Ylli is offline Formula needed; I'm helpless Windows 7 64bit Formula needed; I'm helpless Office 2010 64bit
Novice
Formula needed; I'm helpless
 
Join Date: Dec 2017
Posts: 5
Ylli is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Vorlage Präsenzerefassung 2018.xlsx (283.3 KB, 12 views)
Reply With Quote
  #2  
Old 12-07-2017, 06:02 AM
ArviLaanemets ArviLaanemets is offline Formula needed; I'm helpless Windows 8 Formula needed; I'm helpless Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Into C27 enter the formula
Code:
=IF(C$26=1;1;"")
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.
Reply With Quote
  #3  
Old 12-07-2017, 07:22 AM
Ylli Ylli is offline Formula needed; I'm helpless Windows 7 64bit Formula needed; I'm helpless Office 2010 64bit
Novice
Formula needed; I'm helpless
 
Join Date: Dec 2017
Posts: 5
Ylli is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Into C27 enter the formula
Code:
=IF(C$26=1;1;"")
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.
Reply With Quote
  #4  
Old 12-07-2017, 08:51 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula needed; I'm helpless Windows 7 64bit Formula needed; I'm helpless Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 12-07-2017, 09:28 AM
ArviLaanemets ArviLaanemets is offline Formula needed; I'm helpless Windows 8 Formula needed; I'm helpless Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #6  
Old 12-08-2017, 01:12 AM
Ylli Ylli is offline Formula needed; I'm helpless Windows 7 64bit Formula needed; I'm helpless Office 2010 64bit
Novice
Formula needed; I'm helpless
 
Join Date: Dec 2017
Posts: 5
Ylli is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Sorry I don't understand German - Difficult to help under these circumstances
How so? This is about a formula, you don't have to know what the texts mean

Quote:
Originally Posted by ArviLaanemets View 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.
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.
Reply With Quote
  #7  
Old 12-08-2017, 04:19 AM
ArviLaanemets ArviLaanemets is offline Formula needed; I'm helpless Windows 8 Formula needed; I'm helpless Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Ylli View Post
It always belongs to the column where row 26 has a 1 in it..

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
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.
Reply With Quote
  #8  
Old 12-12-2017, 06:53 AM
Ylli Ylli is offline Formula needed; I'm helpless Windows 7 64bit Formula needed; I'm helpless Office 2010 64bit
Novice
Formula needed; I'm helpless
 
Join Date: Dec 2017
Posts: 5
Ylli is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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
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.
Reply With Quote
  #9  
Old 12-13-2017, 12:32 AM
ArviLaanemets ArviLaanemets is offline Formula needed; I'm helpless Windows 8 Formula needed; I'm helpless Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Look at possible solution, which removes all uncertainty.
Attached Files
File Type: xlsx TableExample.xlsx (9.1 KB, 9 views)
Reply With Quote
  #10  
Old 12-13-2017, 03:31 AM
Ylli Ylli is offline Formula needed; I'm helpless Windows 7 64bit Formula needed; I'm helpless Office 2010 64bit
Novice
Formula needed; I'm helpless
 
Join Date: Dec 2017
Posts: 5
Ylli is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Look at possible solution, which removes all uncertainty.
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.
Reply With Quote
  #11  
Old 12-13-2017, 04:06 AM
ArviLaanemets ArviLaanemets is offline Formula needed; I'm helpless Windows 8 Formula needed; I'm helpless Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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

Tags
complicated, formula, needed

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula needed; I'm helpless Help needed with IF formula with a Vlookup jonnie_rc Excel 6 08-31-2017 01:27 PM
Formula needed; I'm helpless hard formula needed nfsmith Excel 6 03-19-2016 06:12 PM
Formula needed; I'm helpless 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft