Posts by AAE

  • Scatter plot

    Re: Scatter plot


    In Excel, using the default cell format, all numbers are automatically right aligned and all text strings are left aligned.
    Your data are right aligned - hard to tell if this due to a formatting change or if these are really text values that appear as numbers - the "numbers" appear to be data strings.


    The row-1 cells should be header cells.
    Suggest you create a new table with valid header and numbers, using default format or a number format then try to create the chart.

  • Highlight all duplicates after first occurence

    Re: Highlight all duplicates after first occurence


    halo,


    Welcome to Ozgrid.


    I have revised your thread title to accurately define your thread/need.
    Thread titles are used in forum searches. Well defined titles return relevant links - so make a better effort in future threads regarding the title.


    Thanks.

  • matching data

    Re: matching data


    eayyips,


    To understand either formula:


    1. Read the Excel Help on each function used and get a clear understanding of the syntax and arguments
    2. Use the Evaluate Formula tool and step through the formula to see how Excel is executing each argument and the results that are being returned.


    Both formulas are Array Formula - if you are not familiar with them, visit YouTube and watch a few videos on them, many are available.

  • Calculate Working Hours Between 2 Dates Including Saturday

    Re: Calculate Working Hours Between 2 Dates Including Saturday


    at.prashu,


    Posting your questions in threads started by others is known as thread hijacking.
    Always start a new thread for your question; if it helps to clarify your needs you may include to any other thread in the forum.


    Please start your own thread.

  • Calculate sum based on four criteria

    Re: Calculate sum based on four criteria


    Chris,


    I have revised your thread title to more accurately define your thread.
    Please do not include commentary in thread titles, such as "needed to feed . . ."


    Forum searches use titles to return relevant thread so this make accurate titles very important. Try to be more precise in future threads. Thanks.

  • How to Create Alerts to Make Users Add Comments if a Specific Option is Selected from

    Re: How to Create Alerts to Make Users Add Comments if a Specific Option is Selected


    Basic question: if you copied and pasted the formula, did you make sure to capture/include the equal sign. All formulas must include the equal sign at the start.


    Also if you captured a leading space character it will be invisible and force the formula to appear as a text string.
    Try typing it directly in the cell rather than copy paste.


    Check the format of the cell. If it is formatted as text, then you will have to reset the format to General. Select a cell that you know is using the default General format, then use the Format Painter to apply the format of this cell to the target formula cell.

  • OFFSET replacement to get rid of save prompt

    Re: OFFSET replacement to get rid of save prompt


    This formula was just an example - not a working solution.


    First, your data must be in an Excel Table.
    Then you would need to use the actual table name and reference to the applicable table column. The link I gave will get you started on using tables and structured references.


    For more help, do a web search on Excel Tables and Structured References.

  • OFFSET replacement to get rid of save prompt

    Re: OFFSET replacement to get rid of save prompt


    I don't consider it a good practice to reference entire columns, as in G:G.


    You could create a dynamic named range and use it.


    =INDEX(MyRng,ROW()-1)+F5-E5


    Where MyRng is the named range
    =Sheet1!$G$1:INDEX(Sheet1!$G:$G,)


    Or if you are using an Excel Table you could use a structured reference to the table column. Tables are automatically treated as dynamic ranges in Excel, so no need to create a named range.


    =INDEX(Table1[Data1],ROW()-1)+F5-E5

  • Are worksheet names an efficient way to create "variables"

    Re: Are worksheet names an efficient way to create "variables"


    It might be best to upload an example work sheet . . . so that your needs are clearly understood.


    Quote

    I assume it would be much more efficient to calculate these in one place and then have the formulas refer to that, rather than doing something like TODAY() - 180 in every row


    Maybe a named formula will work for you.


    Name: Days180
    Formula: =TODAY()-180


    Then reference the named formula in your table:
    =B2-Days180

  • Conditional Insert Of Page Breaks

    Re: Conditional Insert Of Page Breaks


    baobao,


    Posting your questions in threads started by others is known as thread hijacking.
    Always start a new thread for your question and include a link to any other thread if it helps to clarify your needs.


    Also, please do not quote entire posts in replies. Quote only if necessary and limit the quote to just the relevant phrases.


    Thanks,

  • ounces for excel template?

    Re: ounces for excel template?


    BakeryJess,


    Welcome to Ozgrid.


    Posting your questions in threads started by others is known as thread hijacking.
    Always start a new thread for your questions and include a link to any other thread that helps clarify your needs.

  • Dynamic Cell Ranges Within Formulas

    Re: Dynamic Cell Ranges Within Formulas


    I'm glad PCI's solution is working for you.


    However, I don't see how that formula is meeting your requirement of randomly selecting "any cell" and automatically getting the row number of the selected cell, but perhaps that is not what you wanted/meant - - given you now have a working formula in hand.

  • Dynamic Cell Ranges Within Formulas

    Re: Dynamic Cell Ranges Within Formulas


    Quote

    I'm looking to see if I'm able to enter a formula in any cell, find the row# of the cell that I'm entering the formula in . . .


    Then I randomly click on cell D20. Is there a way I can write a formula that would instantly select A20 as the starting range for my formula . . .


    Dynamically setting the row reference to equal the row of any randomly chosen cell will absolutely require VBA.


    Perhaps if you posted a sample workbook (dummy data) that includes a few examples of your needs and a clear, explicit explanation of your logic, etc. someone may be able to provide a method or suggest a solution that will be feasible.