| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
some help with an IF formual or something like it
Hi all,
I am a bit of a newb so be gentle here... I have a sheet that has a lit of names vertically and weeks of the month horizontally. In my "total" cell I want to displayt he result fo the following: If in the range of cells there is an X - then add 40, add 40 for each x in the range If in the range of cells there is an H - then add 32, add 32 for each H in the range If in the range of cells there is a 1-31 then add 8, if shown as 1,2 (or any combination of 1-31 like 1,5,8,31 then add 32) So, if Max takes the week of May 30th off, I input X into that weeks cell and formula adds 40, if Max takes off the 6th, 7th and 9th of May off, I input 6,7,9 in that weeks cell and the formula adds 24, if Max takes off the 1st and 2nd, I input 1,2 in the cell and the formula adds 16. Is this even possible??? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
some help with an IF formual or something like it
The formula to find the numeric value for a single cell is the
following: =IF(A2="X",40,IF(A2="h",32,IF(A2<"",IF(ISNUMBER(F IND(",",A2)),8*(LEN (A2)-LEN(SUBSTITUTE(A2,",",""))+1),8)))) Now, if you want to find a row total you use this in array form inside SUM and commit with Shift+Ctrl+Enter: =SUM(IF(A2:BA2="X",40,IF(A2:BA2="h",32,IF(A2:BA2< "",IF(ISNUMBER(FIND (",",A2:BA2)),8*(LEN(A2:BA2)-LEN(SUBSTITUTE(A2:BA2,",",""))+1),8))))) If you want a column total, the same for columns =SUM(IF(A2:A101="X",40,IF(A2:A101="h",32,IF(A2:A10 1<"",IF(ISNUMBER (FIND(",",A2:A101)),8*(LEN(A2:A101)-LEN(SUBSTITUTE(A2:A101,",",""))+1), 8))))) HTH Kostis Vezerides On Nov 18, 7:05*pm, Lost Will wrote: Hi all, I am a bit of a newb so be gentle here... I have a sheet that has a lit of names vertically and weeks of the month horizontally. In my "total" cell I want to displayt he result fo the following: If in the range of cells there is an X - then add 40, add 40 for each x in the range If in the range of cells there is an H - then add 32, add 32 for each H in the range If in the range of cells there is a 1-31 then add 8, if shown as 1,2 (or any combination of 1-31 like 1,5,8,31 then add 32) So, if Max takes the week of May 30th off, I input X into that weeks cell and formula adds 40, if Max takes off the 6th, 7th and 9th of May off, I input 6,7,9 in that weeks cell and the formula adds 24, if Max takes off the 1st and 2nd, I input 1,2 in the cell and the formula adds 16. Is this even possible??? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
some help with an IF formual or something like it
Adjust the ranges as necessary...
=40*COUNTIF(B2:AJ2,"X")+32*COUNTIF(B2:AJ2,"H")+8*S UMPRODUCT(ISNUMBER(FIND(",",B2:AJ2))+LEN(B2:AJ2)-LEN(SUBSTITUTE(B2:AJ2,",",""))) This formula can be copied down. -- Rick (MVP - Excel) "Lost Will" wrote in message ... Hi all, I am a bit of a newb so be gentle here... I have a sheet that has a lit of names vertically and weeks of the month horizontally. In my "total" cell I want to displayt he result fo the following: If in the range of cells there is an X - then add 40, add 40 for each x in the range If in the range of cells there is an H - then add 32, add 32 for each H in the range If in the range of cells there is a 1-31 then add 8, if shown as 1,2 (or any combination of 1-31 like 1,5,8,31 then add 32) So, if Max takes the week of May 30th off, I input X into that weeks cell and formula adds 40, if Max takes off the 6th, 7th and 9th of May off, I input 6,7,9 in that weeks cell and the formula adds 24, if Max takes off the 1st and 2nd, I input 1,2 in the cell and the formula adds 16. Is this even possible??? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
some help with an IF formual or something like it
I can visualize it now inside the SUM however I am still getting a message
that the formula contains an error. I have made the cell references match...still a little confused but thanks fro the help.. what is wrong with this now? =SUM(IF(D4:BC4="X",40,IF(D4:BC4="h",32,IF(D4:BC4< "",IF(ISNUMBER(FIND (",",D4:BC4)),8*(LEN(D4:BC4)-LEN(SUBSTITUTE(D4:BC4,",",""))+1),8))))) "vezerid" wrote: The formula to find the numeric value for a single cell is the following: =IF(A2="X",40,IF(A2="h",32,IF(A2<"",IF(ISNUMBER(F IND(",",A2)),8*(LEN (A2)-LEN(SUBSTITUTE(A2,",",""))+1),8)))) Now, if you want to find a row total you use this in array form inside SUM and commit with Shift+Ctrl+Enter: =SUM(IF(A2:BA2="X",40,IF(A2:BA2="h",32,IF(A2:BA2< "",IF(ISNUMBER(FIND (",",A2:BA2)),8*(LEN(A2:BA2)-LEN(SUBSTITUTE(A2:BA2,",",""))+1),8))))) If you want a column total, the same for columns =SUM(IF(A2:A101="X",40,IF(A2:A101="h",32,IF(A2:A10 1<"",IF(ISNUMBER (FIND(",",A2:A101)),8*(LEN(A2:A101)-LEN(SUBSTITUTE(A2:A101,",",""))+1), 8))))) HTH Kostis Vezerides On Nov 18, 7:05 pm, Lost Will wrote: Hi all, I am a bit of a newb so be gentle here... I have a sheet that has a lit of names vertically and weeks of the month horizontally. In my "total" cell I want to displayt he result fo the following: If in the range of cells there is an X - then add 40, add 40 for each x in the range If in the range of cells there is an H - then add 32, add 32 for each H in the range If in the range of cells there is a 1-31 then add 8, if shown as 1,2 (or any combination of 1-31 like 1,5,8,31 then add 32) So, if Max takes the week of May 30th off, I input X into that weeks cell and formula adds 40, if Max takes off the 6th, 7th and 9th of May off, I input 6,7,9 in that weeks cell and the formula adds 24, if Max takes off the 1st and 2nd, I input 1,2 in the cell and the formula adds 16. Is this even possible??? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
some help with an IF formual or something like it
Thanks so much that is perfect.
I am learning and this has expanded my knowledge a little. The help is appreciated. Will// "Rick Rothstein" wrote: Adjust the ranges as necessary... =40*COUNTIF(B2:AJ2,"X")+32*COUNTIF(B2:AJ2,"H")+8*S UMPRODUCT(ISNUMBER(FIND(",",B2:AJ2))+LEN(B2:AJ2)-LEN(SUBSTITUTE(B2:AJ2,",",""))) This formula can be copied down. -- Rick (MVP - Excel) "Lost Will" wrote in message ... Hi all, I am a bit of a newb so be gentle here... I have a sheet that has a lit of names vertically and weeks of the month horizontally. In my "total" cell I want to displayt he result fo the following: If in the range of cells there is an X - then add 40, add 40 for each x in the range If in the range of cells there is an H - then add 32, add 32 for each H in the range If in the range of cells there is a 1-31 then add 8, if shown as 1,2 (or any combination of 1-31 like 1,5,8,31 then add 32) So, if Max takes the week of May 30th off, I input X into that weeks cell and formula adds 40, if Max takes off the 6th, 7th and 9th of May off, I input 6,7,9 in that weeks cell and the formula adds 24, if Max takes off the 1st and 2nd, I input 1,2 in the cell and the formula adds 16. Is this even possible??? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| minimum formual help | Excel Discussion (Misc queries) | |||
| Formual | Excel Worksheet Functions | |||
| WHAT DOES FORMUAL =C8+ 15 DO | Excel Discussion (Misc queries) | |||
| Formual | Excel Discussion (Misc queries) | |||
| Set Formual | Excel Discussion (Misc queries) | |||
Linear Mode
