Posts by mljohn

    I only want to see the current time so I am using NOW() with Shift+Ctrl+2 to only show the current time. However, when I use that time in a formula that finds if that time is within a time range it does not work. When I type the time in manually it works.
    How do I use the NOW() formula with the formula to find if the time is within the time range? =IF($J$16=MEDIAN(R2,S2,$J$16),"Yes","No")

    Re: Dynamically create range 3 years from TODAY for formula


    Quote from skywriter;797548

    Maybe something like this?
    =INDEX(OFFSET(Data!$GM$2,0,-38,1,36),MATCH(TRUE,INDEX(OFFSET(Data!$GM3,0,-38,1,36)<>0,0),0))



    This does not work. I get #N/A


    $GM$2 is the column name "First Ord" , I assume that is not correct but I don't see how to fix it.


    Matt

    My columns are named by month and date "Jun-2017", "Jul-2017", "Aug-2017"


    Cell GO1 formula: =TODAY()



    I will use this report monthly so I want it to be dynamic every time I add a new months column of data.


    In cell GM3 I have this formula. I want the formula to apply only within the last 36 months (last 36 columns) from the GO1 date.


    =INDEX(Data!$B$2:$GJ$2,MATCH(TRUE,INDEX(Data!B3:GJ3<>0,0),0))


    Any thoughts on how to do this would be very much appreciated.


    Matt

    I have this formula that finds the last cell in a row that is greater than $0.00. Then it finds the date header at the top of the column.


    LOOKUP(2,1/INDEX(Data!B4:GI4<>0,0,0),Data!$B$2:$FU$2)


    Can it be adjusted to find the first cell?


    Matt

    I am trying to come up with a formula that gives me all of the part numbers that have prices.
    In Sheet "Orig" I have my data. In Sheet "Result" in column "A" is where I want the results.


    There are two sources. NC Price and PremPrice.
    If there is a price in NC Price for a particular part number I want the part number in a cell in the Result sheet.
    If there is a price in PremPrice for that same part number I want the part number in the next cell down.
    I want a part number for every part number that has a price.


    This one has me banging my head.


    See example below.





    [TABLE="width: 558"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="colspan: 2"]Sheet "Orig"[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Sheet "Result"

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [td][/td]


    [td]

    A

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Brand

    [/td]


    [td]

    Part#

    [/td]


    [td]

    NC Price

    [/td]


    [td]

    PremPrice

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Result

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR110CL

    [/td]


    [TD="align: right"]$75.65[/TD]
    [TD="align: right"]$124.42[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR110CL

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR200

    [/td]


    [TD="align: right"]$34.00[/TD]
    [TD="align: right"]$39.00[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR110CL

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR250

    [/td]


    [TD="align: right"]$37.40[/TD]
    [TD="align: right"]$42.00[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR200

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR300

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR200

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR350

    [/td]


    [TD="align: right"]$21.25[/TD]
    [TD="align: right"]$38.00[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR250

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR360

    [/td]


    [TD="align: right"]$20.40[/TD]
    [TD="align: right"]$39.90[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR250

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR630

    [/td]


    [TD="align: right"]$22.95[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR350

    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR700

    [/td]


    [TD="align: right"]$38.25[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR350

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    Brother

    [/td]


    [td]

    DR720

    [/td]


    [TD="align: right"]$22.10[/TD]
    [TD="align: right"]$42.00[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR360

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    Brother

    [/td]


    [td]

    LC103BK

    [/td]


    [TD="align: right"]$1.45[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR360

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR630

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    9

    [/td]


    [td]

    6

    [/td]


    [td]

    Total

    [/td]


    [td]

    15

    [/td]


    [td][/td]


    [td]

    DR700

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR720

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    DR720

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    LC103BK

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Total

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [/TABLE]

    In Sheet "Orig", Cell B3 I have the part number that I am looking for the price.


    In Sheet "Vendors" I have all of the price lists from all of my vendors.
    In Sheet "Vendors" column "C" I have matching part numbers for all of my vendors.
    In Sheet "Vendors" column A I have the names of the vendors such as below:


    A B C D E
    [TABLE="width: 318"]

    [tr]


    [td]

    Vendor

    [/td]


    [td]

    Brand

    [/td]


    [td]

    Part#

    [/td]


    [td]

    Other#

    [/td]


    [td]

    Cost

    [/td]


    [/tr]


    [tr]


    [td]

    SW

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    106R02307

    [/td]


    [td][/td]


    [TD="align: right"]$52.90[/TD]

    [/tr]


    [tr]


    [td]

    SW

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    108R00795

    [/td]


    [td][/td]


    [TD="align: right"]$36.50[/TD]

    [/tr]


    [tr]


    [td]

    SW

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    108R00795

    [/td]


    [td]

    108R00795

    [/td]


    [TD="align: right"]$46.75[/TD]

    [/tr]


    [tr]


    [td]

    LMI

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    106R01486

    [/td]


    [td][/td]


    [TD="align: right"]$49.00[/TD]

    [/tr]


    [tr]


    [td]

    LMI

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    106R01530

    [/td]


    [td][/td]


    [TD="align: right"]$52.90[/TD]

    [/tr]


    [tr]


    [td]

    LMI

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    106R02307

    [/td]


    [td][/td]


    [TD="align: right"]$59.90[/TD]

    [/tr]


    [tr]


    [td]

    LMI

    [/td]


    [td]

    Xerox

    [/td]


    [td]

    108R00795

    [/td]


    [td][/td]


    [TD="align: right"]$74.90[/TD]

    [/tr]


    [/TABLE]


    I want a formula that finds the matching part number from Sheet "Orig" Cell B3 in Sheet "Vendors" Column "C" and only returns the matching price for that part number from the LMI prices. It is like a Vlookup but would narrow it down to only inclued the search to the LMI rows.



    thanks

    I have a column of part numbers. Each cell in the column has either one or multiple part numbers separated by a comma and space. (Sheet2)


    On Sheet1 I have a cell (A1 - A6) to put in the part number I am looking for. In Column B I have a Vlookup formula to find the price of the part number.


    The Vlookup formula does not work because of the multiple part numbers in the same cell


    How do I create a formula that can pull the correct price for the correct part number even though that part number is combined together with other part numbers in the same cell?


    Here is an example:
    https://www.dropbox.com/s/hmc4gqei8aancl8/Search.xlsx?dl=0


    Notice in Sheet1 Cell A1 and A3 are pulling the same price when they shouldn't.
    Also, A5 and A6 are blank but still pulling the same price.


    Thanks


    Matt

    Re: Count and Average


    Thank you Trebor76 for the formula.


    I was trying to make it harder than it needed to be.


    Glad you understood my example.


    Matt

    Re: Count and Average


    What I don't understand how to do is...


    Find the the months that have amounts greater than zero B3:AK3


    Average the number of months between amounts greater than zero B2:AK2


    For example:


    B2 May 2012, C2 Jun 2012, D2 Jul 2012, E2 Aug 2012, G2 Sep 2012, H2 Nov 2012, I2 Dec 2012
    B6 $224.00, C6 $0.00, D6 $430.00, E6 $0.00, G6 $481.00, H6 $0.00, I6 $280.00
    B7 $0.00, C7 $0.00, D7 $0.00, E7 $55.00, G7 $0.00, H7 $0.00, I7 $110.00


    Answer for this client (A6): 1.75 months
    Answer for this client (A7): 3.5 months



    ozgrid.com/forum/core/index.php?attachment/65518/


    Thanks

    How do I average the number of months the cells that have amounts greater than zero.


    Count months greater than zero within a 36 month period.


    Average the amount of months between the months that are greater than zero.


    Months = B2:AK2 Jan 2002, Feb 2002, Mar 2002, Apr 2002, May 2002, etc
    Cells = B3:AK3 Cells that may contain numbers greater than zero


    Thanks


    Matt