Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Smart calculations with Excel?
I would like to find out,if it is possilbe to create a fomulate that will do :
I enter project names in a row (day name) that has a corresponding column (a 15min timeslot) 11:00 11:15 11:30 11:45 12:00 .... Monday Alpha Beta Omega Alpha Delta Tuesday Beta Beta Alpha .... ... I would like to have the project displayed (in a separate result cell) that occurs the most, hower I would like not to have a numerical value as a result - I would like to have the porject name displayed. Furthermore in an another result cell I would like to have the time calculated that I spent working on that project (my 15 min.time slots). Is it possible to design such a function without doing a UDF? Kind Regards for all suggestions or recommendations. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Smart calculations with Excel?
Hi Christian,
Is the list of project names always drawn from a common list or are they totally random names? If they are drawn from a common list that can exist in the workbook (preferrably on another worksheet) then your request is not too difficult to achieve using the worksheet functions. Regards, OssieMac "Christian" wrote: I would like to find out,if it is possilbe to create a fomulate that will do : I enter project names in a row (day name) that has a corresponding column (a 15min timeslot) 11:00 11:15 11:30 11:45 12:00 .... Monday Alpha Beta Omega Alpha Delta Tuesday Beta Beta Alpha .... ... I would like to have the project displayed (in a separate result cell) that occurs the most, hower I would like not to have a numerical value as a result - I would like to have the porject name displayed. Furthermore in an another result cell I would like to have the time calculated that I spent working on that project (my 15 min.time slots). Is it possible to design such a function without doing a UDF? Kind Regards for all suggestions or recommendations. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Smart calculations with Excel?
Hi OssieMac,
thanks for the quick reply. The project names are generated randomly(by the project managers and I work on them until my share of work is done), but I could create a list of names on the same sheet (or a different one) and keep it updated if new projects would be added to my work portfolio. What is the next step? Kind Regards Christian "OssieMac" wrote: Hi Christian, Is the list of project names always drawn from a common list or are they totally random names? If they are drawn from a common list that can exist in the workbook (preferrably on another worksheet) then your request is not too difficult to achieve using the worksheet functions. Regards, OssieMac "Christian" wrote: I would like to find out,if it is possilbe to create a fomulate that will do : I enter project names in a row (day name) that has a corresponding column (a 15min timeslot) 11:00 11:15 11:30 11:45 12:00 .... Monday Alpha Beta Omega Alpha Delta Tuesday Beta Beta Alpha .... ... I would like to have the project displayed (in a separate result cell) that occurs the most, hower I would like not to have a numerical value as a result - I would like to have the porject name displayed. Furthermore in an another result cell I would like to have the time calculated that I spent working on that project (my 15 min.time slots). Is it possible to design such a function without doing a UDF? Kind Regards for all suggestions or recommendations. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Smart calculations with Excel?
Hi Christian,
Firstly, the formulas are only going to find one MAX. If you have more than one with the MAX value then I dont know how to handle that. However, you will be able to view them on Sheet2 on the lookup table. I really don't know how much help you need so I have gone in for the overkill and hopefully you can sort it out. Assume Sheet1 for your day of week and time of day data as per example you posted. The following is on Sheet2:- Unique list of project names in Column B of Sheet2. Using your example Project Names is the column header and the projects are in cells B2:B5. (Column A blank at this stage) Column B Project Names Alpha Beta Omega Delta In Cell A2 formula:- =COUNTIF(Sheet1!$B$2:$F$4,B2) Where Sheet1!$B$2:$F$4 is the range in Sheet1 containing all of your projects under day of week and time of day. B2 is the first project name (Alpha) in the unique list of project names in column B of on Sheet2. Drag this formula to the bottom of the project names and in column A you will have a list of numbers representing the count of each project name. Should now look like this with numbers in column A and projects in column B:- 3 Alpha 3 Beta 1 Omega 1 Delta In a blank cell under the count of names insert this formula:- =MAX($A$2:$A$5) Where $A$2:$A$5 is the list of numbers representing the count of each project name. It returns the highest number. Should now look like this:- 3 Alpha 3 Beta 1 Omega 1 Delta 3 The following is on Sheet1:- In any blank cell enter the following formula:- =VLOOKUP(Sheet2!$A$7,Sheet2!$A$2:$B$5,2,FALSE) Where Sheet2!$A$7 is the cell reference with the MAX formula. Sheet2!$A$2:$B$5 is the range (2 columns) with the counts of projects plus the project names. 2 represents the value in the second column to be displayed. False means exact match only (not nearest match) Basically the formula looks up the project name with the highest count. In any other blank cell on Sheet1 for total time on max count project:- Custom Format the cell to [h]:mm (The square brackets around the h prevents Excel from displaying the time of the next day if the number of hours exceed 24. Example 30:00 would show as 6:00 because Excel thinks it is 6:00 am the next day.) Insert the following formula for the total time spent on the project with the max count:- =Sheet2!$A$7*TIMEVALUE("0:15") Where Sheet2!$A$7 contains the MAX formula. TIMEVALUE("0:15") simply converts the time entered as text into a numeric value. (You can enter the time in another cell and simply insert the cell reference in place of "0:15".) The VLOOKUP formula and the one to calculate the total time can actually have the MAX formula nested in lieu of placing the max formula in a separate cell and referencing the cell. Examples as follows:- =VLOOKUP(MAX(Sheet2!$A$2:$A$5),Sheet2!$A$2:$B$5,2, FALSE) Calculate the total time on max count project:- =MAX(Sheet2!$A$2:$A$5)*TIMEVALUE("0:15") Hope you have understood all this so far because now I am going to explain how to make it even better. You indicated that the number of project references could grow. Therefore, I suggest that you name the ranges in your lookup sheet (Sheet2) and use the named ranges in your formulas so that when the ranges alter in size all you have to do is rename the ranges and the formulas will still be correct. There are 2 ranges to name. First range:- Occurrences This will apply to Sheet2!$A$2:$A$5 (only one column) Project_Reference This will apply to Sheet2!$A$2:$B$5 (2 columns) The formulas will now look like this where the MAX is nested into the formula:- =VLOOKUP(MAX(Occurrences),Project_Reference,2,FALS E) =MAX(Occurrences)*TIMEVALUE("0:15") Feel free to get back to me if you have any further questions. Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mappoint smart tags in excel - (not working) | Excel Discussion (Misc queries) | |||
Problems between Hyperion Smart View Add-in and Excel | Excel Discussion (Misc queries) | |||
How do I add a stock symbol smart tag in Excel? | Excel Discussion (Misc queries) | |||
smart tags in Excel? | Excel Discussion (Misc queries) | |||
Smart Tag in Excel does not recognize some stock symbols | Excel Discussion (Misc queries) |