User Posts: Osman Goni Ridwan
0
How to Create a Descriptive Statistics Table in Excel
0

You have come to the right place if you are looking for the answer or some unique tips to create a descriptive statistics table in Excel. There are some easy ...

0
How to Create a Complex Formula in Excel (with Easy Steps)
0

You have come to the right place if you are looking for the answer or some unique tips to create a complex formula in Excel. There are some easy steps to ...

0
How to Apply Excel COUNTIF with Pivot Table Calculated Field
0

You have come to the right place if you are looking for the answer or some unique tips to use the COUNTIF function with pivot table calculated field in Excel. ...

0
How to Translate Excel File to English (2 Easy Ways)
0

You have come to the right place if you are looking for the answer or some unique tips to translate Excel file to English. There are some easy ways to ...

0
Excel Data Validation Greyed Out (4 Reasons with Solutions)
0

You have come to the right place if you are looking for the answer or some unique tips to fix the problem when data validation is greyed out in Excel. There ...

0
How to Convert Excel Sheet to Google Sheets (3 Easy Ways)
0

You have come to the right place if you are looking for the answer or some unique tips to convert an Excel sheet to google sheets. There are 3 ways to convert ...

0
If Value Exists in Column Then Copy Another Cell in Excel (3 Ways)
0

You have come to the right place if you are looking for the answer or some unique tips to copy another cell if a value exists in a column in Excel. There are 3 ...

0
Stop Excel from Converting Date to Number in Formula (2 Ways)
0

You have come to the right place if you are looking for the answer or some unique tips to stop Excel from converting date to number in formula. There are ...

0
CSV File Not Opening Correctly in Excel (4 Cases with Solutions)
0

You have come to the right place if you are looking for the answer or some unique tips to fix the issue that the CSV file is not opening correctly in Excel. ...

0
Excel CONCATENATE Showing Formula Not Result (5 Solutions)
0

You have come to the right place if you are looking for the answer or some unique tips to solve the problem when CONCATENATE function is showing the formula ...

0
How to Create Weekly Comparison Chart in Excel
0

You have come to the right place if you are looking for the answer or some unique tips to create a weekly comparison chart in Excel. There are some easy steps ...

0
How to Copy Chart in Excel Without Linking Data (with Easy Steps)
0

You have come to the right place if you are looking for the answer or some unique tips to copy chart without linking data in Excel. There are some ways to copy ...

0
Salary Deduction Formula in Excel for Late Coming (with Example)
0

There are some quick steps to calculate salary deduction for creating a late coming formula in Excel. This article will walk you through each and every step ...

0
How to Calculate Probability Distribution in Excel (with Quick Steps)
0

You have come to the right place if you are looking for the answer or some unique tips to calculate Probability Distribution in Excel. There are some quick ...

0
How to Create Ledger Balance Sheet in Excel (with Easy Steps)
0

You have come to the right place if you are looking for the answer or some unique tips to create a ledger balance sheet in Excel There are some quick steps to ...

Browsing All Comments By: Osman Goni Ridwan
  1. Reply
    Osman Goni Ridwan Oct 11, 2022 at 3:45 PM

    Hello FELICIA SANTOS,
    I hope you are doing well! Here, I have created a dataset as you have described and calculated the total amount spent per category.
    >> Here, you have to insert the name of the category in cell F5.
    >> Then, insert the following formula into cell G5:
    =SUMPRODUCT(D3:D10*(C3:C10=F5))


    Thus, you will get the sum of the amount as per the selected category.

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  2. Reply
    Osman Goni Ridwan Oct 11, 2022 at 1:19 PM

    Hello JULIO!
    Hope you are doing well. In our dataset, Method 7 is working properly without any errors. If you are facing errors, that can be for the following reasons:

    1. If any cells that are used in the TEXTJOIN function exceed 252 characters.
    2. If the output of the TEXTJOIN function exceeds 32672 characters which is the cell limit in Excel.
    3. And, sometimes Excel fails to identify the delimiter as text. For this, you should use delimiters inside the inverted commas and shouldn’t use the CHAR functions to generate any symbols.

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  3. Reply
    Osman Goni Ridwan Oct 3, 2022 at 8:39 AM

    Hello BRYAN!
    As per your question, I have added a new portion in the article which contains the method how you can calculate the accrued time after the completion of probationary period automatically.

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  4. Reply
    Osman Goni Ridwan Sep 12, 2022 at 9:57 AM

    Hello ALLISON!
    you can use the “Use of SUMIF with INDEX-MATCH Functions to Sum under Multiple Criteria” method for your problem. Here, we have used the brand name as criteria and you can substitute it by Year. See this screenshot below:

     two criteria along the top

    While using this method, you have to fill in both criteria. So, you must put both the year’s and month’s values.

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  5. Reply
    Osman Goni Ridwan Sep 12, 2022 at 9:29 AM

    Hello JUSTINA!
    I have an easy solution to your problem. To copy data of range Sheet1 (A10072:AT10801) to range Sheet 2 (A10772:AT10801), you can use this VBA Code.

    Sub copy_to_another_worksheet()
    Worksheets("Sheet1").Range("A10072:AT10801").Copy _
    Worksheets("Sheet2").Range("A10772:AT10801")
    End Sub

    And to copy data of range Sheet 1 (A10802:AT10831) to the range Sheet 2 (A10802:AT10831), use the following VBA code-

    Sub copy_to_another_worksheet()
    Worksheets("Sheet1").Range("A10802:AT10831").Copy _
    Worksheets("Sheet2").Range("A10802:AT10831")
    End Sub

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  6. Reply
    Osman Goni Ridwan Sep 4, 2022 at 2:13 PM

    Hello YINKA! I am very glad to hear that this article has helped you. You will find many more Excel-related articles on http://www.ExcelDemy.com. also, You can share your Excel-related problems with us to get a solution. Send an email at [email protected]
    Thank You!

  7. Reply
    Osman Goni Ridwan Sep 4, 2022 at 1:42 PM

    Hello BORIS!
    You can try to “Multilayer Pie Chart” or “Bar of Pie chart” to solve your problem. You can follow this article to create these types of pie charts for your dataset. Please, go through this article,
    How to Make a Multi-Level Pie Chart in Excel
    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  8. Reply
    Osman Goni Ridwan Sep 4, 2022 at 11:48 AM

    Hello ISH SHARMA!
    You can increase and decrease decimal digits from the format options. But Through this, it will round to the nearest decimal. Suppose, when you round the value 44.41, it will be rounded to 44.4, not 44.5 and for the value 44.45, it will become 44.5.

    To use this method,
    >> Select the cell.
    >> Go to the Home tab in the top ribbon.
    >> Here, in the Number menu, click on the “Increase Decimal” icon to increase the decimal digits after the point, and click on the “Decrease Decimal” to decrease the decimal digits.

    Round decimal digits in Excel

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

  9. Reply
    Osman Goni Ridwan Sep 4, 2022 at 11:28 AM

    Hello N R RAVINDREA!
    Thank you for your suggestion. We are taking this into concern. And, You can share your Excel-related problems in an email at [email protected]

    Thank You!

  10. Reply
    Osman Goni Ridwan Sep 4, 2022 at 10:11 AM

    Hello YINGYANG!
    Syntax of IF function is as follows: =IF(logical_test, [value_if_true], [value_if_false])

    So, for the formula =IF(B2="",NOW(),B2):
    >> logical_test – B2=”” : It is the condition of the IF function. The condition is when cell B2 is blank.

    >> value_if_true – NOW() : This is the output when the cell meets the logical test. The NOW() function gives the present time in the cell.

    >> value_if_false – B2 : When the cell will not meet the criteria, the IF function will return the cell value of B2.

    So, in brief, when cell B2 is empty, the formula will insert the present time in the cell; if it isn’t empty, it will leave the cell as it is.

    I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

    Thank You!

  11. Reply
    Osman Goni Ridwan Sep 1, 2022 at 1:46 PM

    Hello ARIF, to get the dates in the next row use the following code when you will insert values in cells of range B3:I3 and want to auto-populate date and time in cell range B4:I4.

    CODE:

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
                If .Count > 1 Then Exit Sub
                If Not Intersect(Range("B3:I3"), .Cells) Is Nothing Then
                    Application.EnableEvents = False
                    If IsEmpty(.Value) Then
                        .Offset(1, 0).ClearContents
                    Else
                        With .Offset(1, 0)
                            .NumberFormat = "dd mmm yyyy hh:mm:ss"
                            .Value = Now
                        End With
                    End If
                    Application.EnableEvents = True
                End If
            End With
    End Sub

    You can change the cell range as your want. I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with a little more explanation in an email at [email protected]

  12. Reply
    Osman Goni Ridwan Sep 1, 2022 at 12:49 PM

    Hello ABBY!
    You have to change a line in the VBA code that is defining the format of the output.

    Insert this line:
    .NumberFormat = “dd mmm yyyy” instead of this .NumberFormat = “dd mmm yyyy hh:mm:ss”

    So, the code will become as follows-

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("D3:D22"), .Cells) Is Nothing Then
    Application.EnableEvents = False
    If IsEmpty(.Value) Then
    .Offset(0, 1).ClearContents
    Else
    With .Offset(0, 1)
    .NumberFormat = "dd mmm yyyy"
    .Value = Now
    End With
    End If
    Application.EnableEvents = True
    End If
    End With
    End Sub
  13. Reply
    Osman Goni Ridwan Sep 1, 2022 at 11:26 AM

    Hello BEN!
    I think the solution to your problem is already solved in method 1 of this article. To search for partial match, you have the wild cards (*) that have been shown in method 1.
    If the cell C13 contains the value of the search item. Then use the following formula:
    =VLOOKUP(“”&C13&””,$B$4:$C$11,2,FALSE)

    I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with little more explanation in an email at [email protected]

    Thank You!

  14. Reply
    Osman Goni Ridwan Sep 1, 2022 at 10:25 AM

    Hello RJ LENNOX!
    In method 1.3m there have been shown only the formula for the names of the right order and in previous methods, there has been shown formula to get the value of CGPA in the right order. Please insert this formula into cell F7:
    =LARGE($C$5:$C$14,ROWS($F$7:$F7))
    and, drag the fill handle to get the top 5 cgpa values.
    I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with a little more explanation in an email at [email protected]

    Thank You!

  15. Reply
    Osman Goni Ridwan Aug 30, 2022 at 3:35 PM

    Thank you, BOKEP. Thank you for your comment. Read more articles on Exceldemy and share them with your friends and family. Keep supporting us!

  16. Reply
    Osman Goni Ridwan Aug 30, 2022 at 3:32 PM

    Hello AVINASH! I have made a dataset as per your description and solved your problem. You can paste the following formula into the column O and the 3rd row to get the comment from sheet 1 when the criteria are met:

    =IF(AND(F4=Sheet1!C4,Sheet2!H4=Sheet1!D4),Sheet1!E4,"")

    Try this for your dataset and let us know the outcome. Thank You!

  17. Reply
    Osman Goni Ridwan Aug 30, 2022 at 1:52 PM

    Thank you! Shakeel. We are really honored to hear that our articles have helped you. Browse articles of Exceldemy more and share with your friends and family and keep supporting us!

  18. Reply
    Osman Goni Ridwan Aug 30, 2022 at 12:55 PM

    hello, EMIN! Actually, I haven’t understood what you asked in this comment. Can you please mail us the problem with a little more explanation at this address: [email protected].
    We will try our best to solve your problem. thank You! And, keep browsing Exceldemy.

  19. Reply
    Osman Goni Ridwan Aug 30, 2022 at 12:29 PM

    Hello M LAVI! with this code, only the column of the active cell that contains the target cell value will be hidden.
    And, you can copy and paste the drop-down cell to other worksheets without assigning data validation again. If you have anything more to know then inform us in a comment!

  20. Reply
    Osman Goni Ridwan Aug 30, 2022 at 11:31 AM

    Hello JUSTIN! The code is working perfectly from my side and the output has been shown in the practice workbook. And, it is not necessary to add code “Dim Cell as Range” here as we are not working with the cell value. But it is a good practice to declare variables at first.
    We have a large collection of Excel-related blogs that will help you to solve many more problems. Browse them and let us know your opinion in the comment section. Thank You!

  21. Reply
    Osman Goni Ridwan Aug 23, 2022 at 4:48 PM

    Hello SHAWN S FAHRER!
    You can get a quadratic equation from a graph with a few points that you have mentioned here. Follow the steps below for this
    First, create a graph with the points you have.
    Then, Go to the Chart Elements clicking the Plus icon.
    Click on the arrow beside the “Trendline” option.
    Go to the “More Options”
    1

    Here, you will go to a window named “Format Trendline”.
    Select the “Polynomial” option of order 2.
    Mark the checkbox saying “Display Equation on Chart”.

    As a result, you will there will create a trend line following a quadratic equation which is also shown in the graph.

    Try this method and let us know the outcome! Thank You!

  22. Reply
    Osman Goni Ridwan Aug 23, 2022 at 4:28 PM

    Hello MANUJ! You have to insert a value as the set value for cell G3 to use Goal Seek feature.. suppose you have an equation like 5x^3 – 2x^2 + 3x – 21 = 0 then you can change the equation like 5x^3 – 2x^2 + 3x – 6 = 15. Here, we have done this.

    And, yes! you are right that the cubic function gives 3 root values. But using the Goal Seek Feature, you will get only one root. and, in many times, the cubic function may have one real root and 2 complex roots. In these cases, the goal seek feature will give the value of the real root.

    Try these examples and let us know the output. Thank You!

  23. Reply
    Osman Goni Ridwan Aug 21, 2022 at 10:49 AM

    Hello, JACK! For this, you have to add a new conditional Formatting rule. Go to Home tab >> Conditional Formatting >> New Rule.

    Then, Select the “Format Only Cells That Contain” and select the “Blanks” option in the “Format only cells with” box.

    Then, go to the “Format” option and select White as the fill color.

    Try this and let us know the outcome. Thank you!

  24. Reply
    Osman Goni Ridwan Aug 21, 2022 at 10:45 AM

    Hello JUILA! I hope you are fine. You can easily create a conditional drop-down list from other worksheets in the same workbook. You can follow this article-

    https://www.exceldemy.com/excel-drop-down-list-from-another-sheet

    This article will show you the process step-by-step with proper illustrations. Try the methods mentioned in this article and let us know the outcome. Thank you!

  25. Reply
    Osman Goni Ridwan Aug 21, 2022 at 10:43 AM

    Hello APRIL, We already have an article written based on your problem. I hope, you will find this helpful. Follow this link below-

    https://www.exceldemy.com/excel-vlookup-multiple-criteria-without-helper-column/

    Try the methods mentioned in this article and let us know the outcome. Thank you!

  26. Reply
    Osman Goni Ridwan Aug 16, 2022 at 11:41 AM

    Hello Roy! Thank you for your comment. I must say your knowledge of Microsoft Excel is really appreciable. Keep commenting on posts of Exceldemy and help others to learn more.

  27. Reply
    Osman Goni Ridwan Aug 16, 2022 at 11:29 AM

    Hello Johan, you can use the following procedure to serve your purpose-
    – Make a column with 101, 102, 103, and so on.
    – Make a column with A, B, C, D, and repeat.
    Follow this article to repeat numbers or characters serially.
    https://www.exceldemy.com/add-numbers-1-2-3-in-excel/

    – Then use CONCATENATE function to combine them.

    Let us know the outcome in the reply. Thank you!

  28. Reply
    Osman Goni Ridwan Aug 16, 2022 at 11:08 AM

    Hello Nasser Enami, you can follow this article to solve your problem.
    https://www.exceldemy.com/separate-address-in-excel-with-comma

    In this article, you will find how to separate an address into a city, state, and zip code using an Excel formula. You can modify this file for your purpose. In your dataset the separator is “|” and you have to use 3 separators. Modify this worksheet as shown in the screenshot below-

    null

    Let us know the outcome in the reply. Thank you!

  29. Reply
    Osman Goni Ridwan Aug 16, 2022 at 9:48 AM

    hello, you can use the FILTER function to do this. Suppose, you have inserted your dataset into the range B2:D6.
    And, in cell G2. you have inserted the id for what you want to get month and payment.
    so, to get the required data, you have to insert the following formula into any blank cell:

    =FILTER(C3:D6,G2=B3:B6)

    https://www.exceldemy.com/wp-content/uploads/2021/07/SUMPRODUCT-with-INDEX-and-MATCH-Functions-in-Excel-Comment.png

    Use this formula for your dataset and let us know the outcome in the reply. Thank you!

  30. Reply
    Osman Goni Ridwan Aug 16, 2022 at 9:19 AM

    Hello Ahmed, you can go through this article-
    https://www.exceldemy.com/calculate-cost-per-unit-in-excel/

    I hope, you will find your solution. Let us know the outcome by leaving a comment. Thank you!

  31. Reply
    Osman Goni Ridwan Aug 16, 2022 at 9:00 AM

    Thank You, Roy, for your precious comment!

  32. Reply
    Osman Goni Ridwan Aug 4, 2022 at 4:41 PM

    Hello Edward, to find out the total sales for one month, you can follow Criteria 7 of the following article.

    https://www.exceldemy.com/sum-index-match-multiple-criteria-in-excel/

    Here, the Criteria 7 shows how to determine the output based on all Rows & 1 Column with SUM, INDEX, and MATCH functions together. Let us know the result in the reply. Thank you!

  33. Reply
    Osman Goni Ridwan Aug 4, 2022 at 4:20 PM

    Hello SONYA, you can face this issue because of the following reasons:
    1. Incorrect Cell Ranges used in the formula
    2. The Date format used in the formula is not correct. You have to use a similar data format both in the cells and formulas.

    Check these things in your workbook and let us know the outcome. Thanks!

  34. Reply
    Osman Goni Ridwan Aug 4, 2022 at 3:11 PM

    Hello SHANMUGAM, can you please explain your problem a bit more elaborately? Then it will be easy to help you. Thanks!

  35. Reply
    Osman Goni Ridwan Aug 4, 2022 at 2:57 PM

    Hello Peter and Charlie, the solution described in this article at Criteria 2: “Extracting Data Based on 1 Row & 2 Columns with SUM, INDEX and MATCH Functions Together” is perfectly working from our end.
    Just to inform you, you can’t use a cell reference in the Look_up value of the MATCH function instead you have to insert the single of multiple look_up values manually e.g. {“Feb”,”Jun”}. Hope you have found your solution. Try it and let us know the outcome in a reply. Thank you!

  36. Reply
    Osman Goni Ridwan Aug 4, 2022 at 2:04 PM

    Hello, we are glad to hear that our article has helped you. After seeing your comment, I have extended the dataset to 260 rows and the formulas have worked perfectly from my end. Can you please recheck whether you have changed the formula text corresponding to your dataset? For example, in the dataset of 11 rows the formula for largest 5 values will be like
    =LARGE($C$5:$C$14,ROWS($G$7:$G7))
    but when you extended the dataset till 260th row then the formula will be like
    =LARGE($C$5:$C$260,ROWS($G$7:$G7)) .

    If you still face the problem then inform us in the reply. Thank you!

ExcelDemy
Logo