formulas and functions
25373 TopicsLAMBDA : 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 ?18Views0likes1CommentCombinar correspondencia ahora que han desaparecido los DDE con resultados con solo dos decimales
Buenos días: antes combinaba correspondencia de un word con un excell, para hacer mis facturas mensuales con DDE, pero ahora esa opción ha desaparecido y cuando combino me salen muchísimos decimales. He aplicado el formato directamente en Word modificando el campo de fusión con un cambio de formato numérico (por ejemplo, \# "#,##0.00" para forzar dos decimales), pero sigue sin hacérmelo... alguna idea para poder fijar solamente dos decimales en el excell para que los resultados de la combinación en word den solo 2 decimales?42Views0likes1CommentSeries 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 Sub92Views0likes6CommentsMATCH formula anomaly?
Can anyone explain this? If I simply type 490.44 and then calculate 490.44 as 490.44/40*40 (or even directly B3/40*40) excel recognizes both as equal through a simple A=B calculation yet not with a MATCH calculation. What is more odd is any other number I use does not cause this error...Solved113Views0likes3Commentssort by column then by numbers in a row
HI all I have a table of results pictured here. Once the results are in, I sort the table by the Total Score Column. When we have a tie (as in line 3 & 4, I need to sort by who has the most highest score... so, who has the most 9 (equal here), then by most 8.5 (line 4 wins the tiebreaker, 4 over 2) Can I do this so it doesn't affect the order of the columns but just the rows involved.249Views0likes7CommentsRegarding the filter by condition option in pivot table in the excel
I'm taking Google Data analytics course in the coursera, there they use filter in the pivot table and apply conditions like, less than or greater than, they were using the Google sheets, and I couldn't find that option in the excel, you can select or unselect the value in the filter but there's no option to apply conditions like lesser than or greater than such value. Is there any way you guys know, from which you filter the fields by conditions, please let me know.2.5KViews2likes6CommentsExcel 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?89Views0likes1Commentvlookup 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 help38Views0likes1CommentWelcome to the Excel Community
The Excel Community is a place we've built for all of you. You can learn more about how to do something with Excel, discuss your work, and connect with experts that build and use the product. With over half a billion Excel customers, we want to engage with you in fundamentally different ways and the community is a starting point for that. Our community helps answer your product questions with responses from other knowledgeable community members. We love hearing feedback and feature requests from you which helps us build the best version of Excel ever. If you have found an outage or a bug please post at our Answers forum. We look forward to getting to know you! Sangeeta Mudnal & Olaf Hubel on behalf of the Excel Team65KViews30likes95Comments