Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
I am creating an inventory sheet for my business and I'm trying to get Excel
to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
So, the most recent data would be the last (rightmost) numeric value in the
row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
Well, mine is vertical instead of horizontal, but yes, in this screencap it
would be the right most value in any row. Exactly. How did you get that screencap up by the way? That would make things a lot easier. Thanks so much. "T. Valko" wrote: So, the most recent data would be the last (rightmost) numeric value in the row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
How did you get that screencap up by the way?
I use a commercial product for that called Snagit. http://www.techsmith.com/ Well worth it and very easy to use and setup. Then I use a free image host: http://imageshack.us/ You could also upload a sample file (with size restrictions) to a free file host and then post the link to the file. I use this one often: http://translate.google.com/translat...l%3Den%26lr%3D It's a French site that gets translated to English. The file gets removed after about 3 weeks. -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Well, mine is vertical instead of horizontal, but yes, in this screencap it would be the right most value in any row. Exactly. How did you get that screencap up by the way? That would make things a lot easier. Thanks so much. "T. Valko" wrote: So, the most recent data would be the last (rightmost) numeric value in the row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
Why go via Google (aren't they insidious enough), go direct
http://cjoint.com/ -- __________________________________ HTH Bob "T. Valko" wrote in message ... How did you get that screencap up by the way? I use a commercial product for that called Snagit. http://www.techsmith.com/ Well worth it and very easy to use and setup. Then I use a free image host: http://imageshack.us/ You could also upload a sample file (with size restrictions) to a free file host and then post the link to the file. I use this one often: http://translate.google.com/translat...l%3Den%26lr%3D It's a French site that gets translated to English. The file gets removed after about 3 weeks. -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Well, mine is vertical instead of horizontal, but yes, in this screencap it would be the right most value in any row. Exactly. How did you get that screencap up by the way? That would make things a lot easier. Thanks so much. "T. Valko" wrote: So, the most recent data would be the last (rightmost) numeric value in the row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
Yeah, but that's in French. A first-time non-French speaking user probably
won't understand it. -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... Why go via Google (aren't they insidious enough), go direct http://cjoint.com/ -- __________________________________ HTH Bob "T. Valko" wrote in message ... How did you get that screencap up by the way? I use a commercial product for that called Snagit. http://www.techsmith.com/ Well worth it and very easy to use and setup. Then I use a free image host: http://imageshack.us/ You could also upload a sample file (with size restrictions) to a free file host and then post the link to the file. I use this one often: http://translate.google.com/translat...l%3Den%26lr%3D It's a French site that gets translated to English. The file gets removed after about 3 weeks. -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Well, mine is vertical instead of horizontal, but yes, in this screencap it would be the right most value in any row. Exactly. How did you get that screencap up by the way? That would make things a lot easier. Thanks so much. "T. Valko" wrote: So, the most recent data would be the last (rightmost) numeric value in the row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
Anyway to adjust this formula to make it work vertically?
I couldn't work out a VLOOKUP formula, but I imagine that's what you would have to do. I'll try to get a screenshot up here. "T. Valko" wrote: So, the most recent data would be the last (rightmost) numeric value in the row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation Using "Most Current Data Entered"
http://img148.imageshack.us/my.php?i...ventorymv7.jpg
Here's what I'm looking at. The left image is the summary sheet where the formula will be entered. The right image is the actual inventory sheet where the data is drawn from. It would be difficult to make this horizontal just because there's more than one field per day so I need to adjust the previous formula to go vertical. "igotboost" wrote: Anyway to adjust this formula to make it work vertically? I couldn't work out a VLOOKUP formula, but I imagine that's what you would have to do. I'll try to get a screenshot up here. "T. Valko" wrote: So, the most recent data would be the last (rightmost) numeric value in the row? See if this screencap helps: http://img263.imageshack.us/img263/7383/lastnumam1.jpg -- Biff Microsoft Excel MVP "igotboost" wrote in message ... I am creating an inventory sheet for my business and I'm trying to get Excel to look through an inventory sheet, take the most recently entered inventory number, and divide it by the average daily sales to give me a "days out" number. I was going to just use an IF(D1<"",D1,IF(C1<"",C1...etc, etc but the limit to embedded IF statements struck that possibility. I figure there has to be an easier way anyway. Sheet 1 is the summary page where it has each product name, average daily sales, and then the "days out" number. Sheet 2 has each product name horizontally across the top and then the dates vertically on the left for a daily inventory count. I just can't figure out a way to get Excel to pull the most current date's inventory number. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
bunch of "yes" or "no" entered in row, output 1 if a single yes | Excel Worksheet Functions | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
How can I use the "TODAY ()" Function in an "IF/THEN" calculation | Excel Worksheet Functions | |||
=IF((TODAY()-B8)30, "Overdue", "Current"), | Excel Discussion (Misc queries) |