| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table with formula
I need a formula. I need to place c "Time" in appropriate time table.
Corresponding with the appropriate date they came in. On Sheet 1. In Column B1:B2000 I have dates. Columb C1:C2000 i have time" 60" meaning 60 minutes. On Sheet 2 In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90 minutes and 120 minutes. On the right side of the table In Column P2 all the way down i have fixed dates 1/1/08 dragged down all the way to end of year. ( i do not change these dates.) What i want is when i enter date in Sheet1 and the time i want to automatically be entered coresponding in the correct column and date. But insead of transfering the exact number ex "75" to the severyfive minutes column and date. I want it to only count as 1. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Table with formula
My suggestion is to reformat your Sheet 2 a little bit:
B C D E ... P 2 60 75 90 120 Dates 3 FORM. 1/1/08 Then place this formula in B3 and copy it to the right until E3 and down to the end of the year. It's not clear what you have in columns F:H. Regards, Stefi =SUMPRODUCT(--(Sheet1!$B$1:$B$2000=$P3),--(Sheet1!$C$1:$C$2000=B$2)) €˛Jman€¯ ezt Ć*rta: I need a formula. I need to place c "Time" in appropriate time table. Corresponding with the appropriate date they came in. On Sheet 1. In Column B1:B2000 I have dates. Columb C1:C2000 i have time" 60" meaning 60 minutes. On Sheet 2 In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90 minutes and 120 minutes. On the right side of the table In Column P2 all the way down i have fixed dates 1/1/08 dragged down all the way to end of year. ( i do not change these dates.) What i want is when i enter date in Sheet1 and the time i want to automatically be entered coresponding in the correct column and date. But insead of transfering the exact number ex "75" to the severyfive minutes column and date. I want it to only count as 1. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Table with formula
Your formula worked perfectly...
Thanks Much I have a different question not related to the above. If in A4 i have an error message "#value!" how can i get a1 to diplay blank.. I am am using this formula =mode(a1:a16) But in A4 i have "#value!" resulting from another formula that i can not delete. How can i have this formula ignore the "value!" sign. and count the commn denominator between a few numbers. "Stefi" wrote: My suggestion is to reformat your Sheet 2 a little bit: B C D E ... P 2 60 75 90 120 Dates 3 FORM. 1/1/08 Then place this formula in B3 and copy it to the right until E3 and down to the end of the year. It's not clear what you have in columns F:H. Regards, Stefi =SUMPRODUCT(--(Sheet1!$B$1:$B$2000=$P3),--(Sheet1!$C$1:$C$2000=B$2)) €˛Jman€¯ ezt Ć*rta: I need a formula. I need to place c "Time" in appropriate time table. Corresponding with the appropriate date they came in. On Sheet 1. In Column B1:B2000 I have dates. Columb C1:C2000 i have time" 60" meaning 60 minutes. On Sheet 2 In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90 minutes and 120 minutes. On the right side of the table In Column P2 all the way down i have fixed dates 1/1/08 dragged down all the way to end of year. ( i do not change these dates.) What i want is when i enter date in Sheet1 and the time i want to automatically be entered coresponding in the correct column and date. But insead of transfering the exact number ex "75" to the severyfive minutes column and date. I want it to only count as 1. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Table with formula
TYPO;; I MEAN TO SAY
If in A4 i have an error message "#value!" how can i get A4 to diplay blank.. "Jman" wrote: Your formula worked perfectly... Thanks Much I have a different question not related to the above. If in A4 i have an error message "#value!" how can i get a1 to diplay blank.. I am am using this formula =mode(a1:a16) But in A4 i have "#value!" resulting from another formula that i can not delete. How can i have this formula ignore the "value!" sign. and count the commn denominator between a few numbers. "Stefi" wrote: My suggestion is to reformat your Sheet 2 a little bit: B C D E ... P 2 60 75 90 120 Dates 3 FORM. 1/1/08 Then place this formula in B3 and copy it to the right until E3 and down to the end of the year. It's not clear what you have in columns F:H. Regards, Stefi =SUMPRODUCT(--(Sheet1!$B$1:$B$2000=$P3),--(Sheet1!$C$1:$C$2000=B$2)) €˛Jman€¯ ezt Ć*rta: I need a formula. I need to place c "Time" in appropriate time table. Corresponding with the appropriate date they came in. On Sheet 1. In Column B1:B2000 I have dates. Columb C1:C2000 i have time" 60" meaning 60 minutes. On Sheet 2 In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90 minutes and 120 minutes. On the right side of the table In Column P2 all the way down i have fixed dates 1/1/08 dragged down all the way to end of year. ( i do not change these dates.) What i want is when i enter date in Sheet1 and the time i want to automatically be entered coresponding in the correct column and date. But insead of transfering the exact number ex "75" to the severyfive minutes column and date. I want it to only count as 1. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Table with formula
E.g. =VALUE("sss") returns "#value!"
Complete your formula in A4: =IF(ISERROR(VALUE("sss")),"",VALUE("sss")) Regards, Stefi €˛Jman€¯ ezt Ć*rta: TYPO;; I MEAN TO SAY If in A4 i have an error message "#value!" how can i get A4 to diplay blank.. "Jman" wrote: Your formula worked perfectly... Thanks Much I have a different question not related to the above. If in A4 i have an error message "#value!" how can i get a1 to diplay blank.. I am am using this formula =mode(a1:a16) But in A4 i have "#value!" resulting from another formula that i can not delete. How can i have this formula ignore the "value!" sign. and count the commn denominator between a few numbers. "Stefi" wrote: My suggestion is to reformat your Sheet 2 a little bit: B C D E ... P 2 60 75 90 120 Dates 3 FORM. 1/1/08 Then place this formula in B3 and copy it to the right until E3 and down to the end of the year. It's not clear what you have in columns F:H. Regards, Stefi =SUMPRODUCT(--(Sheet1!$B$1:$B$2000=$P3),--(Sheet1!$C$1:$C$2000=B$2)) €˛Jman€¯ ezt Ć*rta: I need a formula. I need to place c "Time" in appropriate time table. Corresponding with the appropriate date they came in. On Sheet 1. In Column B1:B2000 I have dates. Columb C1:C2000 i have time" 60" meaning 60 minutes. On Sheet 2 In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90 minutes and 120 minutes. On the right side of the table In Column P2 all the way down i have fixed dates 1/1/08 dragged down all the way to end of year. ( i do not change these dates.) What i want is when i enter date in Sheet1 and the time i want to automatically be entered coresponding in the correct column and date. But insead of transfering the exact number ex "75" to the severyfive minutes column and date. I want it to only count as 1. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Table with formula
It works well if there is no formula in the cell,
Is it possible to make it work if i already have another formula in the cell. ex. =weekend(A4,2) Can i combine both formulas, and if yes, how. You can use the above formula if you want to give an example. "Stefi" wrote: E.g. =VALUE("sss") returns "#value!" Complete your formula in A4: =IF(ISERROR(VALUE("sss")),"",VALUE("sss")) Regards, Stefi €˛Jman€¯ ezt Ć*rta: TYPO;; I MEAN TO SAY If in A4 i have an error message "#value!" how can i get A4 to diplay blank.. "Jman" wrote: Your formula worked perfectly... Thanks Much I have a different question not related to the above. If in A4 i have an error message "#value!" how can i get a1 to diplay blank.. I am am using this formula =mode(a1:a16) But in A4 i have "#value!" resulting from another formula that i can not delete. How can i have this formula ignore the "value!" sign. and count the commn denominator between a few numbers. "Stefi" wrote: My suggestion is to reformat your Sheet 2 a little bit: B C D E ... P 2 60 75 90 120 Dates 3 FORM. 1/1/08 Then place this formula in B3 and copy it to the right until E3 and down to the end of the year. It's not clear what you have in columns F:H. Regards, Stefi =SUMPRODUCT(--(Sheet1!$B$1:$B$2000=$P3),--(Sheet1!$C$1:$C$2000=B$2)) €˛Jman€¯ ezt Ć*rta: I need a formula. I need to place c "Time" in appropriate time table. Corresponding with the appropriate date they came in. On Sheet 1. In Column B1:B2000 I have dates. Columb C1:C2000 i have time" 60" meaning 60 minutes. On Sheet 2 In Row 2. from B across to H i have names sixty minutes, 75 minutes, 90 minutes and 120 minutes. On the right side of the table In Column P2 all the way down i have fixed dates 1/1/08 dragged down all the way to end of year. ( i do not change these dates.) What i want is when i enter date in Sheet1 and the time i want to automatically be entered coresponding in the correct column and date. But insead of transfering the exact number ex "75" to the severyfive minutes column and date. I want it to only count as 1. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| table formula | Excel Worksheet Functions | |||
| formula {=TABLE(G13;)} | Excel Worksheet Functions | |||
| government table formula | Excel Discussion (Misc queries) | |||
| Pivot table formula | Excel Worksheet Functions | |||
| Trying to get formula to Do table | Excel Worksheet Functions | |||
Hybrid Mode
