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

[santaviga]

HI, Can someone PLEASE HELP!!!!!!!!!!!!!!!!!
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!!!!!

 

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

This returns a value error..

M

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

 

[macropod]

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

 

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

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

 

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

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

 

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

Hi Macropod,

Works a treat.

Thanks a lot, very much appreciated..

Regards,

Mark