- 7,689
- Michigan
- Rallywgn81
I am off for an extended birthday weekend, I'll get back at it Monday and see where this gets me.
Excel question
- Thread starter zujca
- 77 comments
- 13,032 views
- 3,927
- Birmingham, UK
- DG Silva
@ImaRobot I don't think that will work because Column B is the first column in the vlookup rather than the second. To use the vlookup, you'd need to rearrange the columns in the opposite order as it only works left-to-right. Alternatively, you could use IF(INDEX(MATCH, this will allow you to search right to left as well.
I need to have a look at my sheets at work, I know I've got something which will work.
I need to have a look at my sheets at work, I know I've got something which will work.
- 3,927
- Birmingham, UK
- DG Silva
@Rallywagon I managed to grab my spreadsheet and found this formula which should work for you:
What this does is:
If you need to return more than one result at a time, say for example more than one IP address from the same MAC address (which shouldn't be possible), you can use the following formula in E2 instead of E1, and drag it down to multiple cells:
If you're not sure of anything, please feel free to ask away.
Code:
=IFERROR(INDEX(Sheet3!$B$1:$D$1000,MATCH($D$1,Sheet3!$D$1:$D$1000,0),1),"N/A")What this does is:
- Takes the MAC address you've entered in D1 and searches for it in the list in Sheet3
- When it finds the exact match (the '0' in the formula), it remembers the line in the sheet where it found the match
- It then returns the result from the first column in the array (the '1' in the formula)
- If no result is found, it'll return 'N/A' (or anything else you want).
If you need to return more than one result at a time, say for example more than one IP address from the same MAC address (which shouldn't be possible), you can use the following formula in E2 instead of E1, and drag it down to multiple cells:
Code:
=IFERROR(INDEX(Sheet3!$B$1:$B$1000,SMALL(IF(Sheet3!D$1:$D$1000=D2,ROW($D$1:$D$1000)-MIN(ROW($D$1:$D$1000),1,1)),COUNTIF(E$1:$E2,E2))),"N/A")If you're not sure of anything, please feel free to ask away.
Last edited:
- 383
- Portugal
- Serialf
In Excel, is it possible (without scripts) to subtract two lap times and have the result in the same time format?
One lap say 1,23,300 the other 1,23,150 with the result being 0,150 miliseconds. I looked for that in the time formats but could not find a suitable approach
One lap say 1,23,300 the other 1,23,150 with the result being 0,150 miliseconds. I looked for that in the time formats but could not find a suitable approach
- 3,927
- Birmingham, UK
- DG Silva
@Serialf There is, but it's not easy. What you need to do is to split the numbers up to be able to convert it to seconds, then work on the difference. So to see how it works, you need to work on it like this below:
You can use the fill down to put the formulae into the cells correctly. Once everything has been put into the correct cells, you can then hide columns C-E.
If you wanted to be really smart, you could even combine all of the formulae together like this:
If you just wanted to show the difference between the two numbers, irrespective of -/+, you can use =ABS(C2-C1)
Finally, if you wanted to show the difference between the first time and the current line (say for example gap to leader), you could use =(C2-$C$1). When you fill down with this, the first number changes to the current line, but the second number stays on the leader's time.
Code:
Assuming comparative times are in A1:A5 in the format 1:23.300 and the lap time comparison goes into B2:B5
C1 contains =LEFT(A1,FIND(":",A1)-1)
D1 contains =RIGHT(A1,LEN(A1)-FIND(":",A1))
E1 contains =(C1*60)+D1
B2 contains =E2-E1You can use the fill down to put the formulae into the cells correctly. Once everything has been put into the correct cells, you can then hide columns C-E.
If you wanted to be really smart, you could even combine all of the formulae together like this:
Code:
C1 contains =(LEFT(A1, FIND(":",A1)-1)*60)+(RIGHT(A1,LEN(A1)-FIND(":",A1)))
B2 contains =C2-C1If you just wanted to show the difference between the two numbers, irrespective of -/+, you can use =ABS(C2-C1)
Finally, if you wanted to show the difference between the first time and the current line (say for example gap to leader), you could use =(C2-$C$1). When you fill down with this, the first number changes to the current line, but the second number stays on the leader's time.
Last edited:
- 3,927
- Birmingham, UK
- DG Silva
Thanks a lot @DG_Silva ! Explained like you did it is very easy. Honestly I though of that way also, but never did it as I was convinced that the "date" formats still could provide a "built-in" solution.
It works fine, thanks again
No problem at all, glad to help. As it is, it's quite useful because it teaches me something I never knew before!
- 3,927
- Birmingham, UK
- DG Silva
@Serialf I did discover a simpler way of doing the above, but it only works where the times are consecutively slower than the next - for example, a qualifying times sheet. In B2 you can use =TEXT(A2-A1,"mm:ss.000"), and carry it down, remembering to use $A$1 if comparing it to the fastest time. If it comes across a time which is faster than the previous, it'll return an error.
- 383
- Portugal
- Serialf
@DG_Silva, I have been away for some days, I got back to the issue and, suddenly I had the revelation 💡, it was all about using the right FORMAT, because excel does the arithmetics, which are so simple as SUM (and substract):
1. FORMAT all cells, only need 5 ou six columns and rows with Custom format, not date or time: mm:ss,000;
2. Enter the times (3 splits of a fast lap, for instance) in A1, A2, A3;
3. Enter the times to be compared in B1, B2, B3;
4. In cells E1, E2, E3 just enter =A-B, and so on and those cells will return the differences in the sectors of the lap, and so where to shave some time
(You can also use $ references, formulas SUM, etc, I just put the basic stuff)
So the key to my problem was in fact the decimal regional format for Portugal (for the miliseconds); I had tried many other variations but not the comma, silly of me.
So, I dont know if I was clear to you (and all readers) in my first post, it was fun to try your suggestions and that was how I found the solution.
Thanks a lot
btw: I am Silva too, thats my last name 👍
1. FORMAT all cells, only need 5 ou six columns and rows with Custom format, not date or time: mm:ss,000;
2. Enter the times (3 splits of a fast lap, for instance) in A1, A2, A3;
3. Enter the times to be compared in B1, B2, B3;
4. In cells E1, E2, E3 just enter =A-B, and so on and those cells will return the differences in the sectors of the lap, and so where to shave some time
(You can also use $ references, formulas SUM, etc, I just put the basic stuff)
So the key to my problem was in fact the decimal regional format for Portugal (for the miliseconds); I had tried many other variations but not the comma, silly of me.
So, I dont know if I was clear to you (and all readers) in my first post, it was fun to try your suggestions and that was how I found the solution.
Thanks a lot
btw: I am Silva too, thats my last name 👍
- 14,424
- Los Angeles, CA
- Lma_robot
- lImaRobotl
I know that technically you can't show a percentage increase from 0, but is there an equation that would make this sort out anyways? It doesn't have to be technically correct, I just want to sort of see if I can force it to show a percentage increase anyways.
It's for a spreadsheet for my Operations Manager showing sales increase from 2017 to 2018, and it just looks ugly seeing a bunch of errors for things that haven't sold in one year or the other, or both. Would it just be easier writing it in without an equation?
It's for a spreadsheet for my Operations Manager showing sales increase from 2017 to 2018, and it just looks ugly seeing a bunch of errors for things that haven't sold in one year or the other, or both. Would it just be easier writing it in without an equation?
- 20,681
- TenEightyOne
- TenEightyOne
- 14,424
- Los Angeles, CA
- Lma_robot
- lImaRobotl
Because there's hundreds of lines that I would have to do individually because of the different sales data, that would take more time than just individually writing in the values that my OM was looking for. I was hoping that there would be a quick cheat to get it done, but thanks nonetheless!
- 20,681
- TenEightyOne
- TenEightyOne
- 14,424
- Los Angeles, CA
- Lma_robot
- lImaRobotl
Although I didn't mention it, I was going to be turning it in yesterday. I actually just left it as the other operations manager has to review it so he said he'll figure out what to do with those for now. Whether he leaves it or does something else I'm not sure, but thanks for the help.
- 20,681
- TenEightyOne
- TenEightyOne
- 20,681
- TenEightyOne
- TenEightyOne
- 7,689
- Michigan
- Rallywgn81
Setting the quadrant variables (a1 for amount, b1 for months) and using them in a formula that you click and drag down the sheet for however many months?
Last edited:
- 20,681
- TenEightyOne
- TenEightyOne
Quite, but I thought I'd implied that there would be a source for amount and months, Matej was asking how to evenly split an amount... which is equated division. He hasn't said if he wants to lookup from an internal/external source so I was asking if there was more information.
Matej
Premium
- 2,002
- Zagreb
Let's say a person has to receive a specific amount of money (e.g. $2000) in four equal instalments, one per month. The amount of instalment depends on how many days he worked. If the person fully worked all four months that's $500 per month, but what if he worked only 10 days in the last month? At the end of all four months he has to receive $2000. That means that in first three months he will receive more than $500 to compensate for the amount lost on the rest of 20 days he didn't work. I need a formula that helps me calculate this.
- 7,689
- Michigan
- Rallywgn81
I mean, Excel is a fine tool, but I dont think they have created the ability to see into the future yet. With the way you want this sheet setup, you won't be able to pay them until all 4 months have been worked, unless you know ahead of time what their schedule will be exactly for those 4 months, which I am guessing you dont know.Let's say a person has to receive a specific amount of money (e.g. $2000) in four equal instalments, one per month. The amount of instalment depends on how many days he worked. If the person fully worked all four months that's $500 per month, but what if he worked only 10 days in the last month? At the end of all four months he has to receive $2000. That means that in first three months he will receive more than $500 to compensate for the amount lost on the rest of 20 days he didn't work. I need a formula that helps me calculate this.
Now, it being the case you do know the future, then, you would divide the amount by total hours worked. 160 hours a month, 640 hours for 4 months. 2000/640.... adjust the 640 to however many hours they actually worked, that's the hourly earning on the 2000, then spread that out over the time they worked each day.
- 20,681
- TenEightyOne
- TenEightyOne
Yep, that's why every payroll department would pay something like this in arrears, particularly if it's a taxable amount.
Matej
Premium
- 2,002
- Zagreb
We do know the "future", lots of contracts already tell you how much money you are going to get, the only difference is that this amount gets split over the entire work period and every instalment is paid after certain part of the work has been completed rather than paying all at once, the latter which is not practical in most cases I've encountered.
Okay, let me check if I got that right: he worked only 6 days in the last month, that's 48 hours. If he had worked 640 hours (all four months fully) he would have received 3,125 per hour, right? So he earns only $150 for that last month? For the first three months he would receive $616,66 and in total that is $2000 (actually, $1999,98, but that's okay). I still feel I missed something here.
- 20,681
- TenEightyOne
- TenEightyOne
So in that case it's not really a problem that you can solve with Excel. If there are agreed part-payments for project gates then you simply need to track which gates have been made (e.g. which pre-agreed sections of work are complete) and keep a total which you subtract from the total final payment. If you're paying per-hour then you can't make the payment until you know how many hours were worked.
Again, there seems to be a confusion in what kind of contract this employee is working to. Have they been funded for a number of hours within a fixed time period, are they being funded for job completion (and stages thereof), or something other arrangement?
@Rallywagon's completely correct, but as you seem to imply it looks a bit odd when you look at the earning rates in each period.
- 10,943
- Sweden
- eran0004
Let's say a person has to receive a specific amount of money (e.g. $2000) in four equal instalments, one per month. The amount of instalment depends on how many days he worked. If the person fully worked all four months that's $500 per month, but what if he worked only 10 days in the last month? At the end of all four months he has to receive $2000. That means that in first three months he will receive more than $500 to compensate for the amount lost on the rest of 20 days he didn't work. I need a formula that helps me calculate this.
This month’s pay = 2000*[number of days worked this month]/[total number of days worked over the period of four months]
- 7,689
- Michigan
- Rallywgn81
This needs to divided out by the hour. As Matej was pointing out, a contractor may work 8 hours in a day, or they may work 6, or just 4. This is why you need future sight to properly fill out the spreadsheet, you may know how much they are to make over the extent of the contract, but you dont know how many hours they will put in each week until they put the hours in. You dont know that they are going to work 48 hours the first week, or 20 the last.
What can be done is, a 40 hour work week can be assumed and that can be spread out across the "timesheets" days. Then another column can be added that can be used to adjust the hourly rate from there. That way if they work over 8 hours, or under eight hours you just need the add the + or - to that cell and it can add or subtract however much from the total earning for that day.
- 10,943
- Sweden
- eran0004
Actually it sounds like it’s based on contracts with a fixed sum, so there is no need to count worked hours. Just base it on planned workdays if the goal is to distribute a contract sum over a longer period of time.
You can easily base it on planned hours as well though, just count hours instead of days.
Matej
Premium
- 2,002
- Zagreb
Sorry for not responding, I've been busy. I think I figured it out, at least manually. Equal split of $2000 on four months is $500 per month. But if a guy worked only 10 days in the last month, he would receive only $166,6 (500 / 30 = 16,66 and then 166,66 * 10 = 166,6). That means if the instalment for the last month is now reduced, others in the three remaining months have to be equally increased now to get $2000. I did 2000 - 166,6 = 1833,4 and then 1833,4 / 3 = 611,13.
There is probably a good formula that can summarize all this, but I can't figure out which one it would be.
There is probably a good formula that can summarize all this, but I can't figure out which one it would be.
