HELP!!!! on formula PLEASE!!!!!!!!!!!!

[santaviga]

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

[macropod]

Hi santaviga,

Try something like:
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1>70)*(RIG
HT(Interventions!C19:C81,1)="M"),1,))
and
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1>70)*(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(Interventions!C19:C81)-1)^1>5)*(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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[santaviga]

This returns a value error..

M

"macropod" wrote:

> Hi santaviga,
>
> Try something like:
> =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1>70)*(RIG
> HT(Interventions!C19:C81,1)="M"),1,))
> and
> =SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1>70)*(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(Interventions!C19:C81)-1)^1>5)*(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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

[macropod]

Hi santaviga,

That suggests your 'Interventions!C19:C81' range isn't completely filled
with age/*** 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,LEN(Interventions!C19:C81)-1
)^1>70)*(RIGHT(Interventions!C19:C81,1)="M"))),1,))
etc. or with an ISERROR' test, like:
=SUM(IF(ISERROR((LEFT(Interventions!C19:C81,LEN(Interventions!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" <[email protected]> wrote in message
news:[email protected]...
> This returns a value error..
>
> M
>
> "macropod" wrote:
>
> > Hi santaviga,
> >
> > Try something like:
> >
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1>70)*(RIG
> > HT(Interventions!C19:C81,1)="M"),1,))
> > and
> >
=SUM(IF((LEFT(Interventions!C19:C81,LEN(Interventions!C19:C81)-1)^1>70)*(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(Interventions!C19:C81)-1)^1>5)*(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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

[Pete_UK]

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

[santaviga]

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
>
>

[Pete_UK]

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:

=3DSUM(IF(Data=3D"",0,IF((RIGHT(Data,1)=3D"M")*(VALUE(LEFT(Data,LEN(Data)=
=AD-1))>=3DF$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 >=3DF$1, so that it includes the lower value in the count.

Hope this helps again.

Pete

[santaviga]

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(LEFT(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
>
>

[Pete_UK]

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

[santaviga]

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
>
>

[macropod]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

[santaviga]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
>
>
>