Search results

Hello PomDave, Let us know your feedback and solution.

Hello Marktuan, In Excel 365, a simple and efficient way is to combine XLOOKUP with MAXIFS. Assuming your data is in A2 (ID, Date, Value) and the lookup ID is in E2, use: =XLOOKUP(MAXIFS(B:B,A:A,E2),B:B,C:C) Or, to make sure it returns the latest value for the specific ID only: =XLOOKUP(...

Hello AprilLynnRN, Thank you so much for the kind words. Glad to hear it worked! The spill error is expected here. Excel Tables do not support spilled array formulas inside the table body. A formula like FILTER, UNIQUE, SORT, or TRANSPOSE(FILTER(...)) needs open worksheet cells to spill into...

Hello AprilLynnRN, Since you're using Excel 365, I think you're actually very close to a formula-only solution and may not need Power Query at all. If your source data is in an Excel Table named ScheduleTbl with columns Dates and Event, you can first create a list of unique dates...

Hello PomDave, Yes, this is possible with a small VBA user-defined function. Excel formulas cannot directly count cells by font color, so a macro/UDF is the best option. Please try this VBA function: Function CountTextByFontColor(rng As Range, colorCell As Range) As Long Dim cell As Range...

Hello Maps, Glad to hear that the issue is resolved now. You are most welcome.

Hello Maps, Try this formula there: =MIN(D17*5%,215*((YEAR($C$23)-YEAR($C$31))*12+MONTH($C$23)-MONTH($C$31)+1)) For your spreadsheet, it calculates: YTD Gross Salary = 68,515.71 5% of YTD Gross = 3,425.79 July to April = 10 tax periods Maximum deductible = 215 × 10 = 2,150 So the YTD tax...

Hello TrevorWestbrook, Thanks for your feedback. Keep contributing on the ExcelDemy forum to help each other.

Hello Maps, It is possible to show both: the rebate applicable for the current month (Column C), and the year-to-date rebate value (Column D). For the monthly rebate (April 2026 in Column C), use: =IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/365*C22 Where: C21 =...

Good day. You can place the rebate formula in a separate cell and then reference that cell inside the PAYE formula. For example, if: Date of Birth is in C21 Pay Date is in C23 Then the rebate formula can be: =IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100)) This...

Hello Maps, Yes, this can be done. Since your tax year runs from July to June, use the tax period start and end dates in your sheet. For the year-to-date PAYE calculation using your Column D values, use this formula: =LET( YTDGross,$D$17, YTDDeduct,$D$19, DOB,$C$21, PayDate,$C$23...

Hello Maps, Yes, it is possible. You can use one dynamic formula that checks the employee’s salary bracket and age automatically. For your current spreadsheet, use this formula in the PAYE/tax cell...

Hello Zebrabeside, You can follow the same Excel structure shown in the previous reply. The External Financing Needed (EFN) can be calculated using the balance sheet equation: EFN=Projected Assets−(Debt+Equity+Projected Net Income) For your example: Projected Sales = 9,996 Growth factor =...

Hello Lee, Thanks for sharing the sample. In your sheet, the values in column E are being treated as normal time values (hh:mm:ss), so Excel displays them as clock times instead of elapsed durations. For this type of data, you can use a custom format that supports accumulated time properly...

Hello Maps, Yes, using your spreadsheet as the practical example, the portal value of 652.99 can be matched. In your sheet: Gross Salary = 6849.81 Tax Deduction = 215.00 Taxable Pay = 6849.81 - 215 = 6634.81 Days in Month = 30 Days in Year = 365 So: Annualized Taxable Income: = 6634.81 / 30...

Good morning. Thank you for the clarification, and no problem at all. That makes much more sense now. Since the portal result is actually 652.99, your manual calculation is likely very close to the correct logic already. Yes, it should be possible to work backward from the final figure to...

Hello Maps, I checked the workbook you attached. The issue seems to be related to the annualization/proration calculation rather than the tax table itself. Your sheet currently returns 652.99, but the taxman portal gives 634.81 because the portal likely: annualizes the salary using an exact...

Hello Andrewalwin, Thanks for your helpful input. Yes, I agree. For this type of inventory tracker in Google Sheets, Apps Script is more reliable than a normal timestamp formula because formulas can recalculate and change the timestamp later. A practical setup would be: • Use an entry sheet...

Hello Rjr, Yes, this can definitely be done with VBA and Worksheet Change events. You actually have two separate tasks: 1. Perform automatic calculations in columns N:S whenever a value is entered in column M. 2. Maintain a running sequential count for rows where column M contains a numeric...

Hello Dariourzua, Glad to hear that you solved the issue. If you need further assistance, please reach out to us.