Search results

Think I need to learn about aggregates too - cheers for that :)

How about using pivot tables? The data wouldn't need the index, match or sum ifs that slow down processing, and sorting becomes simple

Thank you for that. My next goal in excel is to understand array formulas, I'm getting there slowly

Thanks for that. So I understand it and can adapt to use elsewhere, the formula in M3 is this: =MIN(IFERROR(IF((YourTable[Date]>TODAY())*(FLOOR(M5:M679,5000)<>FLOOR(YourTable[Paid Off?],5000)),YourTable[Date]),99999999)) There is no built in formula for a MINIF so an array is used. The IF...

Hi there I've created a loan sheet detailing payment dates and how much has been paid off. I want to celebrate when I first reach each multiple of $5,000 (i.e $5,000,$10,000, $15,000 etc) How do I write a formula without a helper column to return the next payment date where a multiple of $5,000...

Thank you so much :)

Thanks Chihiro for your response. How do I amend these formulas to deal with the unpaid break? Not all lines will have an unpaid break. I have amended the file so the issue is clearer. I couldn't get the 7pm to 7am formula to work in the original.

Hi Everyone, I'm having a bit of trouble pulling together a time formula to calculate night rate. Night rate is payable for all hours worked between 7pm and 7am. In the file I've attached I've adjusted the time to include the dates, but am getting stuck with the multitude of variables. The...

You both rock! Cheers muchly

Hi there In excel 2007, what do I write if I want to create the custom format to change this $1,200.00 to $1.2 K, but also takes $600.00 and changes to $0.6 K? If I write this $#, K the result is rounded, so both examples above show as $1 K. Cheers Sara

Narayan - you are a genius, and what's more, I can understand all your formulas!

Hi Narayan All the logic stated is spot on. My attempt handles this with lots of helper columns, I just got stuck on the number part at the end (Col Y:AA) Col H deals with the irrelevant lines Col I and J pull the 6 digit code and type Col K - X run the text to column function in formulas...

Hi Narayan Sorry for not being clear. Column A is the area to split. From this I need to pull lines that start with a 6-digit number or "All" The line you specified and the one immediately below it: 882210 MgrSpec WH2 Outwards 0.00 240.00 0.00 All 24.00 1,440.00 1.67 need to become RC...

Hi There I have a particularly nasty PDF file to deal with every month which spans many many pages. I've created a copy and paste area in a spreadsheet which then runs a formula based Text to Columns. From this I need to pull selected data - the initial 6 digit numeric code - the type -...

Hi there You could change the formula in F13 to this =AVERAGEIF(F3:F11,">"&0,F3:F11) Combining the Average and IFError would only handle the error on the result of the average. Using AverageIF and setting the criteria to greater than 0, it will only pick up numbers. Or change the formula...

Thanks Narayan You're wonderful!

Hi there I'm trying to tidy up a SUMIFS formula. Is it possible to shorten the following: =ROUND((SUMIFS(PayData[Units],PayData[Account],"800000",PayData[Paycode],"&#60;&#62;"&"STDHR",PayData[Paycode],"&#60;&#62;"&"*W*",PayData[Period End...

Hi there Date calcs are tricky to get your head around. This formula should work for you. =SUMIFS('[GOS 8-12-13.xlsx]GOS - Detail(1)'!$AB:$AB,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$E:$E,$D$3,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$Y:$Y,"&#62;="&$D$1,'[GOS 8-12-13.xlsx]GOS -...

Unfortunately we're not allowed to do that in the average workplace, however tempting

Hi there This might help http://chandoo.org/wp/2010/04/12/excel-formulas-are-not-working/

Hi RK Due to problems at my end I can't open your file. Have you tried the NETWORKDAYS formula? http://chandoo.org/excel-formulas/networkdays.shtml

Try this =ROUND(IFERROR(AVERAGE(C2,J2,Q2,X2,AE2,AL2),0),0) Regards Sara

Hi Wendy, How about using the "Group" in pivot to do this. http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/

Hi Thakur You might want to read this: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting As for the wonderful chart that Narayan uploaded you may find the following helpful Data Labels: http://chandoo.org/wp/2010/05/05/change-data-labels-in-charts/...

Hi there If you are using 2007 onwards, on the design tab, there is "Report Layout". Change this to show in Tabular Form. This will put all your Row Labels on the same row. It might affect how your subtotals show, so if you want some subtotals but not others, select the label, and on the...