Posts by mljohn

Re: Average time for last 3 years

Is there a way to see the average amount of time between each order for the last 3 years?

Re: Average time for last 3 years

Did this not make sense?

I have a large database of all the purchases that my customers have done.

It is an item that they have to reorder.

I want to try and forecast when my customers might order next.

I figure that if I could have an average amount of time in months that they have been ordering for the last 3 years, then that might help me forecast their next order.

Is there a formula that can do this?

Column A = Companies
Columns DV-FF = Purchase history for last 3 years by month.

forum.ozgrid.com/index.php?attachment/65399/

Thanks

Re: Sumif match in two sheets

Thank you.

Will I need to manually update the formulas every time I add more data or is there another way?

Re: Sumif match in two sheets

Thank you this works great and I think I understand why and how even though I still don't understand the purpose of INDEX

I noticed that the INDEX needed to have all of the data included to work. (Data!$B$3:$BI$20)

Does this mean that as the data grows month by month, more customers and more months of data, that I will have to manually change the formula to keep it accurate?

Is there a way to overcome this?

Thanks

Re: Sumif match in two sheets

The Data tab is for my data dump

I want to put my results in the Results sheet instead of being in the Data Sheet

I don't know how to match the results to the Data sheet

I understand Vlookup but that won't work here to total everything (Data!B3:FA3) for "Consolidated Direct Mail Service"

I want the formula to match the company name from the Results sheet and then total everything for that company from the Data Sheet.

forum.ozgrid.com/index.php?attachment/65279/

I have a sheet in a workbook that contains all data called "Data"

In another sheet called "Results" I want to have the results
A1 Companies
B1 Total $
A3 =Data!A3 This shows a list of all the companies

In B3 I want to sum up Data B:FA if the company names match

I know the formula has something to do with "SumIf", "Match" and maybe "Index" but I still don't get it yet.

Can you help?

Matt

Re: Find last non zero cell in row 2 and show column label in row 1

Quote from Krishnakumar;720905

Hi

Using Excel formula

=LOOKUP(2,1/INDEX(B2:AF2<>0,0,0),$B$1:$AF$1)

This works just fine.

Thanks

I have multiple rows with columns from B:AF
Row 1 has all of the column labels. Jan 12, Feb 12, Mar 12 etc.
Each cell in the row has a number of either zero or a higher number.
Example:
B1, Jan 12 = B2, 0.00
C1, Feb 12 = C2, 75.00
D1, Mar 12 = D2, 0.00

How do I create a formula that finds the last non zero number and then shows the date label at the top of the column (row 1)?

C1 in the example is Feb 12
C2 in the example is 75.00

So the formula would return the answer in AI2 of "Feb 12" for row 2

Thanks

I also posted this in MrExcel but no solution yet.
http://www.mrexcel.com/forum/e…-row-1-a.html#post3880325

Re: How to count number of years

royUK
How do I apply it so that I can drag it down?

I have multiple customer activities with each customer's activities separated by a blank row. before and after each customer's data.

I have a large list of customer's activities.
Each customer is separated by one blank row.
Some customers have been around longer than others so they have more rows of data than others.

When I create a formula, what do I add to the formula to calculate only for the customers data that is between the blank rows? (one before and one after)

This means that when I calculate the data for each customer I want the results to be on the first line of the customer's data for each customer.

Example:

Blank row
Customer A---55.00-----321.00
Customer A---39.00
Customer A---200.00
Customer A---27.00
Blank row
Customer B---189.00----278.00
Customer B---78.00
Blank row

In the example above I want to SUM for each customer. But how do I tell Excel to calculate for only Customer A at C2 and Calculate for only Customer B at C7?

Hope this makes sense. I've seen people do it but I don't know how.

Thanks

Re: How to count number of years

royUK thanks for the formula. I understand formulas better.

It worked.

Thanks again.

I am trying to find out how to count the number of years.

Example

[TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]10/20/2008[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]09/18/2008[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]12/11/2007[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]12/11/2007[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]12/11/2007[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]07/06/2006[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]07/06/2006[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]08/30/2005[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]09/11/2003[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]09/11/2003

[/TD]

[/tr]

[/TABLE]
I show that there are 5 years represented.
I want to show 5 as the number of years not the number of years between the first and last dates.

How do I do this?

Thanks

I have a spreadsheet designed to show me which of my suppliers has the lowest price for the product numbers entered.
At the total I have a formula that only totals if all of the items have a price from that particular supplier. If not it is blank. =IF(SUMPRODUCT(--(ISNUMBER(E3:E25)))=ROWS(E3:E25),SUM(E3:E25),"")
It looks for a null "" in the cells that it is totaling.
If one of the suppliers does not have an item the cell has a null entered. =IF($C3="",0,IFERROR((VLOOKUP($C3,Supplier2!A:B,2,FALSE)*$B3),""))
This works fine.

The problem is in the H column that tells me which supplier has the lowest price.
With this formula I get a #VALUE!
=IF(G3=D3+D$27,$D$1, IF(G3=E3+E$27,$E$1, IF(G3=F3+F$27,$F$1, "none")))

How do I solve this?

Thanks

Matt

Re: sum if no blanks in matching cells

Thanks

Re: sum if no blanks in matching cells

Thanks

Re: sum if no blanks in matching cells

I got it to work.
I had to change the formula in D & E cells to =IF(C3="",0,IFERROR((VLOOKUP(C3,Supplier1!C:G,5,FALSE)*B3),""))
I changed the cell formatting to not show zeros so that I could see the prices better
I used Robert Mika's formula =IF(SUMPRODUCT(--(ISNUMBER(D3:D25)))=ROWS(D3:D25),SUM(D3:D25),"")

This enabled me to see the total for only the suppliers that had pricing for all of the item numbers and for the suppliers that did not have a price for all of the item numbers the total is blank.

Thanks to Robert Mika and Rowddawg

Matt

Re: sum if no blanks in matching cells

Robert,

how do I post a workbook?

In column C the cells are empty or in other words there is nothing in them. The Cells in column C is where I enter the item number that I am looking for.
In Column D there is a Vlookup formula to another sheet that shows that suppliers pricing for that item number. For example the cell in D3: =IFERROR((VLOOKUP(C3,Supplier1!C:G,5,FALSE)*B3),"") B3 is quantity
In Column E it is the same as Column D but just for a different supplier price list. For example the cell in E3: =IFERROR((VLOOKUP(C3,Supplier2!G:M,7,FALSE)*B3),"")

So all of the "Blank" cells would have a formula in them except the cells in Column C. I've changed them to "Empty" in my example.
The reason that columns D and E would be blank is because 1) there is no item number entered in column C or 2) there is no matching price for that item number in the suppliers price sheet.

Hope this helps. Sometimes it is hard to explain these.

Thanks

Re: sum if no blanks in matching cells

Robert your formula almost worked but because there are also blank cells between the items and the total it returned a blank cell.

Thanks

Matt

Re: sum if no blanks in matching cells

Thank you Rowddawg for the formula.

I don't think that I have explained my situation correctly.

I am trying to total columns "D" and "E"

But I only want to total columns "D" and "E" if column "C" is not blank.

Please note that there may be several blank cells in columns "D" and "E" if "C" is blank.

Thanks

Matt