formulas and functions
25349 TopicsCalculating and adding time
I am trying to figure out how to automatically calculate time differences for a delayed racing start time. I have: a rating which provides a handicap correction factor: PHRF Tod A Sec/ Mile correction number A Total correction in Seconds (based on a distance) This I figured out. I have a set start time for the first entry at 11:00:00 I need to figure out the Start time for the delayed boats as shown in the diagram, but I don't know what formulas will do this. This diagram is taken from a pdf document.Solved147Views0likes5CommentsCombining information from 3 cells into One - Street Address Issue
I get a spreadsheet every day with the number of the address in one column the name of the street in another column and the city in 1/3 column and I'd like to take all of this data and put it into one cell without having to copy and paste it. How can I get the three fields merged into one?15KViews0likes2CommentsOverlapping times within one row
Hello, What I would like to do is identify if two time ranges within the same row overlap with each other. My ideal end goal would look like: Start 1 End 1 Client Start 2 End 2 Service Overlap? 8:55am 8:59am NAME 12:09pm 12:19pm SERVICE FALSE 10:01am 10:48am NAME 10:00am 11:10am SERVICE TRUE 10:17am 11:17am NAME 10:40am 10:58am SERVICE TRUE 10:30am 11:00am NAME 10:30am 11:14am SERVICE TRUE 10:50am 11:05am NAME 2:01pm 2:38pm SERVICE FALSE 10:50am 11:00am NAME 9:00am 10:05am SERVICE FALSE I have attempted methods like SUMPRODUCT to identify overlaps, but each one appears to work until I double check and find that it does not identify all overlapping times. For instance, these two rows will be listed as false, even though the times are overlapping. 9:59am 11:04am NAME 10:00am 10:45am SERVICE FALSE 9:58am 10:55am NAME 9:45am 10:25am SERVICE FALSE Thank you in advance for your help!Solved42Views0likes3CommentsExcel - COUNTIF Function
Happy Friday! I am trying to use the COUNTIF Function on a worksheet that tracks how much liquid chlorine is used at 3 different water wells each month, each well having 2 chlorine cylinders. I'm not sure if my brain is fried from reading/watching all kinds of videos for the past 3 hours and trying to follow their instructions, but any time I put in a formula, I get a pop-up saying I've entered too many arguments. Even if I enter individual cells and use only the 3 that it highlights for me (more than 3 does not highlight). I've attached a screen shot of the worksheet (Figure 1) and have highlighted the area I'm trying to figure out. As you can see, it will be for multiple dates, on multiple lines, going through to the end of the month, with the total chlorine (CL2) tallied up on the right hand side. Chlorine tanks are changed out quite often and each tank begins at 150, depleting down to 0. Figure 2 shows a screen shot of what I'm getting when trying to enter the formula. Am I misunderstanding how the formula works or am I using the wrong formula all together? Figure 1 Figure 2Solved126Views0likes4CommentsFill out other cells based on selection
Hello all, brainstorming some ideas. Want to make it easier for my colleagues to fill out. Currently for review section I made it into a dropdown list so user can select from a list than typing it out every time. What i want is based on the selection in 'Review Section', I want it to populate the sample size and total. I could do a switch statement based on what was chosen but i want to see if there is another easier option. Trying something new, any suggestions are welcomed. My Org did restrict VBA so I can't use those. tySolved64Views0likes2CommentsWhat is this Madness? Weird Average and Sum results
What is this devil math? What am I missing? The darker green value and blue value should match. I checked for truncated decimal values, even rebuilt the sample to ensure nothing was weird about the original cells. This is not an insignificant difference for what I'm doing, and I don't understand why these aren't lining up. I'm sorry if I'm missing something simple, but please, help!133Views1like3CommentsProfit/Loss per Day Calendar
Hi All, I am looking for formula for these 2 items. 1. Want total Profit/Loss per date in Calendar (E.g. Total Profit on 02 Feb 2022 was -90.65). 2. If Profit Cell to be Green, If Loss cell to be Red Link to Excel Sheet. https://1drv.ms/x/s!AlvQCyQGg78NgkNEsm2PYzAfJIEo?e=bF7ctc10KViews0likes15CommentsXlookup with nested IF
Thank you in advance for your help. What I am trying to do is return a value (using Xlookup) but only if another value matches. Maybe I need to use Index/Match, but here's my example: Col A Col B Col C Col D Yes John Smith 1.50 No John Smith 1.25 So, I'm trying to enter an Xlookup that will search for "Smith" in column C and return Col D, but only if Col A is also "Yes". I tried =XLOOKUP("Smith",$C:$C,IF($A3="yes",$D:$D,"-"),"-") but I get a "#Value" error. For real life application, I would be replacing "Smith" with a cell reference and "yes" with another cell reference. But for the example, if A=yes, then return D if Col C=Smith. I just need to figure out in which order xlookup (or index/match) would look for those. Any help is appreciated.6KViews0likes6CommentsMargin calculator for media budget
Hi, I need help formulating a calculator where the 'Fixed Margin' is separated out from the total budget. My calculation needs to add a buffer to the margin as the platform where this is inserted will reduce the nominal margin when you also include a flat cpm fee as it increases the total amount billed. For example, if margin was 20% of $10,000 then the remaining budget left should be $8,000. However, when you add a rate card on top and deliver x amount of media impressions, the effective margin as a percent of total cost is always going to be less than the nominal margin percentage. Example: Total cost: $10,000 Margin fee: 20% Flat CPM rate: $1.50 Impressions: 2,000,000 then this would lead to this: Media Cost $5,600 Margin Fee $1,400 Flat CPM rate ($1.5) $3,000 Total $10,000 Ideally, I would like to ensure the margin is kept at $2,000 (20%) so need help creating a formula that will raise the margin to a % that keeps your nominal margin rate whilst factoring the flat cpm fee and the impressions served.130Views0likes4Comments