Forum Widgets
Latest Discussions
Help with Formular to keep constants
Hi I have 2 rows for percentage values as per this table. C3 will be the value of (C2 + B3) / 2. However I need to check for an empty value so my calculation is =IF($B$3="",0, IF($C$2="",0,($C$2+$B$3)/2)) - I hope that is right, A B C D E F G 1 Name Name Name Name Name 2 50.25% 70.78% 60.14% 24.86% 53.64% 3 Name 60.12% 55.18% 65.45 4 Name 45.58% 47.91% I then need to copy(drag) the cells across and down and provide the correct values, so it uses the same top value and left value. For example D3 will be =IF($B$3="",0, IF($D$2="",0,($D$2+$B$3)/2)). C4 will be =IF($B$4="",0, IF($C$2="",0,($C$2+$B$4)/2)) and so which will alter as I drag down or left and it keeps using the correct column value and row value based on what column/row it is in. What I am doing now does not retain the right column or row value. I hope I make sense. Thanks.
LAMBDA : function generating #NAME error
Hie, I juste discovered Lambda function, and i have a problem. I created a lambda function that compute a cubiqual polynomial with the code : Poly3:=LAMBDA(_a, _b, _c, _d, _x, ((_a*_x+_b)*_x+_c)*_x+_d) Excel accepte de function, but when putting the function in a cell, it generate a #NAME error. I Debug the Cell, and it seems that de fith argument is refused, with #NAME error ! but the data pasted to the function is a basic computed value ! I don't see anithing rong in my function. Does anyone have an idear, or explaine me what rong in this code ?
Summarising a number of sheets to a front sheet.
I have a simple spreadsheet with Sheets for individual investment details. The TABS contain the name of the investments eg BP I am trying to put together the front sheet summarising certain data from the individual sheets, one row per sheet. Data such as Investment Name and Value. How can I do this please?
Excell function problem
I have been using Excel for a good few years, i have a spreadsheet that has been working fine until now. My main problem is that when i copy & paste data everything works fine, i have been able to add cells to make a new total. but when i cut & paste the value of cells i get a REF error. so when i need to move a series of cells higher or lower any cells that contain word they move fine, the cells containing numbers move but the cell with the function just goes to #REF, the function was working okay with my previous versions of Excel but now it will not. if anyone has got any idea what the problem is or any way of fixing it i would be most gratefullExcel 365: Unexpected Predictive Behaviour Causing Retroactive Cell Changes in Structured Dataset
Hi everyone, I’m posting this to share an unusual and potentially important issue I encountered in Excel 365 this morning. I’ve already submitted a detailed report to Microsoft via the Feedback Hub, but I felt it would be useful to document it here for visibility and to see whether anyone else has experienced something similar. While entering data into a structured table (a parts‑catalogue workbook), Excel began exhibiting behaviour that did not match Flash Fill, AutoFill, or the standard predictive typing feature. Specifically: Grey predictive text appeared beyond the cursor and extended into cells below the active cell Excel began rewriting previously validated cells in the same column Each time I pressed Enter, Excel updated earlier rows based on its latest inferred pattern Attempts to correct earlier rows caused further cascading changes Undo did not revert all changes The behaviour resembled an AI‑style inference engine trying to “learn” my pattern, becoming progressively more confused This was not the normal predictive typing behaviour (which only shows grey text inside the active cell and does not affect other cells). The behaviour stopped only after I clicked the Flash Fill button twice, which suggests an internal state reset. I’m not seeing the issue now, and other workbooks behaved normally, so this appears to have been a transient internal mode rather than a persistent setting. I’m sharing this here because: it caused silent data corruption in a structured dataset it may indicate an experimental feature or feature‑flag activation others working with structured data may want to be aware I’d be interested to know if anyone else has seen similar behaviour If helpful, I can provide the reproduction steps, screenshots, and the engineering‑style report I submitted to Microsoft. Thanks, John
Series fill a formula down a column automatically skipping a set number of rows
I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it from "spilling" I need the formula to increment every 6 rows, having one blank row between each section. I have been able to accomplish "copying" it each 6th row with VBA #1 and filling the series with VBA #2 but can't figure out how to combine the two. Any help would be appreciated. VBA #1: Sub FillEvery6thCell() Dim ws As Worksheet Dim startRow As Long, lastRow As Long, col As String Dim formulaText As String Dim r As Long ' Set your sheet and parameters Set ws = ThisWorkbook.Sheets("Top 5 Employees") col = "A" ' Column to fill startRow = 10 ' First row to start filling lastRow = 60000 ' Last row to fill ' Get the formula from the starting cell formulaText = ws.Range(col & startRow).Formula ' Fill every 6th cell For r = startRow + 6 To lastRow Step 6 ws.Range(col & r).Formula = formulaText Next r Range("A2:A60000" & iRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 MsgBox "Formula copied to every 6th cell in column " & col End Sub VBA #2 Sub FillFormulasDown() Dim sourceCell As Range Dim fillRange As Range ' Define the cell containing the formula Set sourceCell = Range("A2") ' Define the target range Set fillRange = Range("A2:A5000") ' Fill formulas down sourceCell.AutoFill Destination:=fillRange, Type:=xlFillSeries End SubExcel table not using updated formula in new rows
I have an Excel spreadsheet that keeps giving me the same issue with the formulas in column O. Originally, column O contained the following formula: Original formula: =IF(R4<>"", R4, Q4) + IF(ABS(I5-I4) < 30, 25/1440, IF(ABS(I5-I4) <= 50, 40/1440, IF(ABS(I5-I4) <= 150, 60/1440, IF(ABS(I5-I4) <= 250, 80/1440, IF(ABS(I5-I4) <= 300, 100/1440))))) The formula has since been updated to: Current formula: =IF(R4<>"", R4, Q4) + IF(ABS(I5-I4) < 30, 25/1440, IF(ABS(I5-I4) <= 50, 40/1440, IF(ABS(I5-I4) <= 150, 60/1440, IF(ABS(I5-I4) <= 250, 80/1440, IF(ABS(I5-I4) <= 300, 100/1440))))) + C5/24 However, whenever I insert a new row, Excel automatically fills it with the original formula instead of the updated one, even though I have been using the updated formula for quite some time now. How can I make Excel recognize and use the updated formula when new rows are inserted?
vlookup error
i used vlookup in my spreadsheet, but doesnt work ... case: i have a column a of 151 vehicle number , now i have to find principal outstanding from two column b & c table containing 196 vehicle number with principal outstanding , as i insert formula of =vlookup(a1,b1:c196,2,false) and it gives the desired result but then the problem occurs whhen i drageed formula, the dragged formula also dragged the table_array cell (like in a9,b9:c207,false), and when the vehicle number is store before the table_array it shows #N/A, i.e. if a9 contain vehicle number x and if x is at b4, then formula doesnt work... pl help
