Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!!
I am using the following count to count how many time a number above 70 is input, I need to add M or F onto the end of the numbers. e.g. 74M, the formula i'm using does not recognise both number and text. this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70") I need to add a letter M and F onto the end of this formula to recognise input of e.g. 74M, 86M, 95F to place in correct cells on a different worksheet. 70+ Males for M & 70+ Females for F I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for female and age ranges these are against medicines. So e.g. I want to input 56M into a cell I want this to count up into cell labeled Male 30-70 and so on, keep counting the number of times ranges are entered to M or F the same if I enter 74F I want this to count in the cell labelled Female 70+. Hope you can understand this, thanks for the HELP!!!!! Can anyone HELP!!!!!!!! Its doing my head in... Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Hi santaviga,
Try something like: =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG HT(Interventions!C19:C81,1)="M"),1,)) and =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG HT(Interventions!C19:C81,1)="F"),1,)) both entered as array formulae (ie Ctrl-Shift-Enter). for intermediate age ranges, the formulae could become: =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^15)*(LEFT (Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1<16)*(RIGHT(Interventi ons!C19:C81,1)="M"),1,)) etc, depending on what's supposed to happen with ages like 5.5, 15.5, and so on. Cheers "santaviga" wrote in message ... HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!! I am using the following count to count how many time a number above 70 is input, I need to add M or F onto the end of the numbers. e.g. 74M, the formula i'm using does not recognise both number and text. this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70") I need to add a letter M and F onto the end of this formula to recognise input of e.g. 74M, 86M, 95F to place in correct cells on a different worksheet. 70+ Males for M & 70+ Females for F I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for female and age ranges these are against medicines. So e.g. I want to input 56M into a cell I want this to count up into cell labeled Male 30-70 and so on, keep counting the number of times ranges are entered to M or F the same if I enter 74F I want this to count in the cell labelled Female 70+. Hope you can understand this, thanks for the HELP!!!!! Can anyone HELP!!!!!!!! Its doing my head in... Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
This returns a value error..
M "macropod" wrote: Hi santaviga, Try something like: =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG HT(Interventions!C19:C81,1)="M"),1,)) and =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG HT(Interventions!C19:C81,1)="F"),1,)) both entered as array formulae (ie Ctrl-Shift-Enter). for intermediate age ranges, the formulae could become: =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^15)*(LEFT (Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1<16)*(RIGHT(Interventi ons!C19:C81,1)="M"),1,)) etc, depending on what's supposed to happen with ages like 5.5, 15.5, and so on. Cheers "santaviga" wrote in message ... HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!! I am using the following count to count how many time a number above 70 is input, I need to add M or F onto the end of the numbers. e.g. 74M, the formula i'm using does not recognise both number and text. this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70") I need to add a letter M and F onto the end of this formula to recognise input of e.g. 74M, 86M, 95F to place in correct cells on a different worksheet. 70+ Males for M & 70+ Females for F I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for female and age ranges these are against medicines. So e.g. I want to input 56M into a cell I want this to count up into cell labeled Male 30-70 and so on, keep counting the number of times ranges are entered to M or F the same if I enter 74F I want this to count in the cell labelled Female 70+. Hope you can understand this, thanks for the HELP!!!!! Can anyone HELP!!!!!!!! Its doing my head in... Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Hi santaviga,
That suggests your 'Interventions!C19:C81' range isn't completely filled with age/sex data. If you have anything other than '#X', where '#' is a number and 'X' is a single letter, or the cell is empty, the formulae I gave you in my previous post won't work. To cope with such conditions, re-code the formulae with 'NOT' & ISERROR' tests, like: =SUM(IF(NOT(ISERROR((LEFT(Interventions!C19:C81,LE N(Interventions!C19:C81)-1 )^170)*(RIGHT(Interventions!C19:C81,1)="M"))),1,) ) etc. or with an ISERROR' test, like: =SUM(IF(ISERROR((LEFT(Interventions!C19:C81,LEN(In terventions!C19:C81)-1)^1 70)*(RIGHT(Interventions!C19:C81,1)="M")),,1)) etc. (note the change in the position of the '1' at the end of the formula). Cheers "santaviga" wrote in message ... This returns a value error.. M "macropod" wrote: Hi santaviga, Try something like: =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG HT(Interventions!C19:C81,1)="M"),1,)) and =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^170)*(RIG HT(Interventions!C19:C81,1)="F"),1,)) both entered as array formulae (ie Ctrl-Shift-Enter). for intermediate age ranges, the formulae could become: =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventi ons!C19:C81)-1)^15)*(LEFT (Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1<16)*(RIGHT(Interventi ons!C19:C81,1)="M"),1,)) etc, depending on what's supposed to happen with ages like 5.5, 15.5, and so on. Cheers "santaviga" wrote in message ... HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!! I am using the following count to count how many time a number above 70 is input, I need to add M or F onto the end of the numbers. e.g. 74M, the formula i'm using does not recognise both number and text. this is the formula i'm using: =COUNTIF(Interventions!C19:C81,"70") I need to add a letter M and F onto the end of this formula to recognise input of e.g. 74M, 86M, 95F to place in correct cells on a different worksheet. 70+ Males for M & 70+ Females for F I have cells with headings Male 0-5 6-15 16-30 30-70 70+ the same for female and age ranges these are against medicines. So e.g. I want to input 56M into a cell I want this to count up into cell labeled Male 30-70 and so on, keep counting the number of times ranges are entered to M or F the same if I enter 74F I want this to count in the cell labelled Female 70+. Hope you can understand this, thanks for the HELP!!!!! Can anyone HELP!!!!!!!! Its doing my head in... Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
To model your problem I did the following:
I put the word "Input" in A1, and defined a named range called "Data" covering A2 to A20 - obviously you might need to extend this range, depending how much data you have. I put this test data in A2 to A9: 21F 56M 74F 3M 33F 47M 65F 66F I put these values in cells F1 to K1: 0, 6, 16, 31, 71, 200, and these headings in F2 to J2: 0-5 6-15 16-30 31-70 71+ I put the word "Male" in E3 and "Female" in E4, and in F3 I entered this array formula*: =SUM(IF(Data="",0,IF((RIGHT(Data,1)=LEFT($E3,1))*( VALUE(LEFT(Data,LEN(Data)-1))F$1)*(VALUE(LEFT(Data,LEN(Data)-1))<G$1),1,0))) * As this is an array formula, once you have typed it in (or subsequently edit it), you must use CTRL-SHIFT-ENTER rather than just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you must not type these yourself. The formula can then be copied to G3:J3 and to F4:J4. The result is this table in E2:J4 0-5 6-15 16-30 31-70 71+ Male 1 0 0 2 0 Female 0 0 1 3 1 You should be able to change the references to suit your own circumstances. Hope this helps. Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Hi Pete_UK,
Thanks for this it works a treat but cant figure out how to put it into practise on my cells, I need to take the data from another sheet and put it onto a summary sheet, can this formula include data from another sheet, also instead of reference cells can these be replaced by text?? Thanks for your help!!! Mark "Pete_UK" wrote: To model your problem I did the following: I put the word "Input" in A1, and defined a named range called "Data" covering A2 to A20 - obviously you might need to extend this range, depending how much data you have. I put this test data in A2 to A9: 21F 56M 74F 3M 33F 47M 65F 66F I put these values in cells F1 to K1: 0, 6, 16, 31, 71, 200, and these headings in F2 to J2: 0-5 6-15 16-30 31-70 71+ I put the word "Male" in E3 and "Female" in E4, and in F3 I entered this array formula*: =SUM(IF(Data="",0,IF((RIGHT(Data,1)=LEFT($E3,1))*( VALUE(LEFT(Data,LEN(Data)-1))F$1)*(VALUE(LEFT(Data,LEN(Data)-1))<G$1),1,0))) * As this is an array formula, once you have typed it in (or subsequently edit it), you must use CTRL-SHIFT-ENTER rather than just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you must not type these yourself. The formula can then be copied to G3:J3 and to F4:J4. The result is this table in E2:J4 0-5 6-15 16-30 31-70 71+ Male 1 0 0 2 0 Female 0 0 1 3 1 You should be able to change the references to suit your own circumstances. Hope this helps. Pete |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
The named range "Data" can be on your Interventions sheet. From what
you have above I think your range is C19 to C81, so highlight these cells on that sheet and Insert | Name | Define and type Data for the name (it should confirm what this refers to in the bottom panel). If you want to refer specifically to "M" and "F", then you can make this change to the formula in F3: =SUM(IF(Data="",0,IF((RIGHT(Data,1)="M")*(VALUE(LE FT(Data,LEN(Data)*-1))=F$1)*(VALUE(LEFT(Data,LEN(Data)-1))<G$1),1,0))) This can be copied across, but you would need to copy it into F4 and change the "M" to "F" before copying across - don't forget to do CSE if you edit the formula. The other cell references in the formula assume the layout I described earlier, i.e. the formula is in F3 and it refers to F1 and G1 where the values to determine the lower and upper limits are, so you can change these to suit where your formula resides. NOTE that I have changed the F$1 to =F$1, so that it includes the lower value in the count. Hope this helps again. Pete |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Kind Regards Pete, not doing ma head in now, thanks for the HELP!!!! thought
there would have been an easier way to do it e.g with countif and just enclose all criteria in the formula rather than have links to cells, I wouls never have worked this formula out. Mark "Pete_UK" wrote: The named range "Data" can be on your Interventions sheet. From what you have above I think your range is C19 to C81, so highlight these cells on that sheet and Insert | Name | Define and type Data for the name (it should confirm what this refers to in the bottom panel). If you want to refer specifically to "M" and "F", then you can make this change to the formula in F3: =SUM(IF(Data="",0,IF((RIGHT(Data,1)="M")*(VALUE(LE FT(Data,LEN(Data)Â*-1))=F$1)*(VALUE(LEFT(Data,LEN(Data)-1))<G$1),1,0))) This can be copied across, but you would need to copy it into F4 and change the "M" to "F" before copying across - don't forget to do CSE if you edit the formula. The other cell references in the formula assume the layout I described earlier, i.e. the formula is in F3 and it refers to F1 and G1 where the values to determine the lower and upper limits are, so you can change these to suit where your formula resides. NOTE that I have changed the F$1 to =F$1, so that it includes the lower value in the count. Hope this helps again. Pete |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Mark,
Thanks for feeding back. COUNTIF only works on a single condition and you have 3 - Male/Female, lower age and upper age. You could probably have used SUMPRODUCT, but I'm more familiar with array formulae. Glad to be of help. Pete |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Hi Pete,
Still having problems, just noticed it when I need to input more data into cells above and below e.g 74M. the way the cells are set out is as follows: Cell Heading - Epi PRF No: 1234567 Date: 01/05/2006 Age/gender: 74M Case Type: Collapse Outcome: POS Dose 1mg This is how the layout is in the cells on interventions, so when I input this data I get an error ?value as the cells contain other than 74M or 56F, is there a way I can edit the formula so that it counts only the cells that contain 56M and not come up with an error for inputting the other data, there are approx 50 cells that will contain e.g. 74M. Sorry to be a pain. Thanks again for the help. much appreciated. Mark "Pete_UK" wrote: Mark, Thanks for feeding back. COUNTIF only works on a single condition and you have 3 - Male/Female, lower age and upper age. You could probably have used SUMPRODUCT, but I'm more familiar with array formulae. Glad to be of help. Pete |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Hi santaviga,
With these headings in F2 to J2: 0-5 6-15 16-30 31-70 71-199 plus the word "Male" in E3 and "Female" in E4, enter this array formula in F3: =SUM(IF(ISERROR((RIGHT(Data)<"F")*(RIGHT(Data)<" M")*LEFT(Data,LEN(Data)-1) ^1),0,IF((LEFT(Data,LEN(Data)-1)^1=LEFT(F$2,FIND("-",F$2)-1)^1)*(LEFT(Data, LEN(Data)-1)^1<=RIGHT(F$2,LEN(F$2)-FIND("-",F$2))^1)*(RIGHT(Data,1)=LEFT($E3 ,1)),1,))) and copy across/down to J4. That should be enough to trap any likely error condition. Cheers PS: This formula doesn't require Pete's F1-J1 range, and you can change the '199' to any other suitable maximum age. The formula also allows you to easily add more age ranges or change them "santaviga" wrote in message ... Hi Pete, Still having problems, just noticed it when I need to input more data into cells above and below e.g 74M. the way the cells are set out is as follows: Cell Heading - Epi PRF No: 1234567 Date: 01/05/2006 Age/gender: 74M Case Type: Collapse Outcome: POS Dose 1mg This is how the layout is in the cells on interventions, so when I input this data I get an error ?value as the cells contain other than 74M or 56F, is there a way I can edit the formula so that it counts only the cells that contain 56M and not come up with an error for inputting the other data, there are approx 50 cells that will contain e.g. 74M. Sorry to be a pain. Thanks again for the help. much appreciated. Mark "Pete_UK" wrote: Mark, Thanks for feeding back. COUNTIF only works on a single condition and you have 3 - Male/Female, lower age and upper age. You could probably have used SUMPRODUCT, but I'm more familiar with array formulae. Glad to be of help. Pete |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!!! on formula PLEASE!!!!!!!!!!!!
Hi Macropod,
Works a treat. Thanks a lot, very much appreciated.. Regards, Mark "macropod" wrote: Hi santaviga, With these headings in F2 to J2: 0-5 6-15 16-30 31-70 71-199 plus the word "Male" in E3 and "Female" in E4, enter this array formula in F3: =SUM(IF(ISERROR((RIGHT(Data)<"F")*(RIGHT(Data)<" M")*LEFT(Data,LEN(Data)-1) ^1),0,IF((LEFT(Data,LEN(Data)-1)^1=LEFT(F$2,FIND("-",F$2)-1)^1)*(LEFT(Data, LEN(Data)-1)^1<=RIGHT(F$2,LEN(F$2)-FIND("-",F$2))^1)*(RIGHT(Data,1)=LEFT($E3 ,1)),1,))) and copy across/down to J4. That should be enough to trap any likely error condition. Cheers PS: This formula doesn't require Pete's F1-J1 range, and you can change the '199' to any other suitable maximum age. The formula also allows you to easily add more age ranges or change them "santaviga" wrote in message ... Hi Pete, Still having problems, just noticed it when I need to input more data into cells above and below e.g 74M. the way the cells are set out is as follows: Cell Heading - Epi PRF No: 1234567 Date: 01/05/2006 Age/gender: 74M Case Type: Collapse Outcome: POS Dose 1mg This is how the layout is in the cells on interventions, so when I input this data I get an error ?value as the cells contain other than 74M or 56F, is there a way I can edit the formula so that it counts only the cells that contain 56M and not come up with an error for inputting the other data, there are approx 50 cells that will contain e.g. 74M. Sorry to be a pain. Thanks again for the help. much appreciated. Mark "Pete_UK" wrote: Mark, Thanks for feeding back. COUNTIF only works on a single condition and you have 3 - Male/Female, lower age and upper age. You could probably have used SUMPRODUCT, but I'm more familiar with array formulae. Glad to be of help. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |