Need a formula to count the total number of characters within a spreadsheet
cell as opposed to the number of words. All help much appreciated.
Hi,
In E8 down I have a gender column. In this column the user inputs either M or F (for male or female)
The next three columns have labels headed wt , wa and wb (working toward, working at and working beyond). The user inputs a Y if they are working at any of these 3 levels.
I want to a formula to calculate how many males and females are working toward, working at and working beyond.
Thanks for your help.
Hi all,
I would like to calculate the number of intervals between the first and last cells in time series, arranged as below:
Sheet5
A
B
C
D
E
1
Time
Interval
Genus 1
Genus 2
Genus 3
2
1
Barremian
3
2
Aptian
3
4
3
Albian
5
5
4
Cenomanian
32
6
5
Turonian
2
14
7
6
Coniacian
5
8
7
Santonian
3
9
8
Campanian
44
10
9
Maastrichtian
113
11
10
Danian
1
7
2
12
11
Selandian
13
14
15
Desired
16
Max Interval
Aptian
Turonian
Albian
17
Max Time
2
5
3
18
Min Interval
Danian
Danian
Danian
19
Min Time
10
10
10
20
21
# Intervals
8
5
7
Spreadsheet Formulas
Cell
Formula
C21
=C19-C17
D21
=D19-D17
E21
=E19-E17
Excel tables to the web >> Excel Jeanie HTML 4
I have been using this formula
=INDIRECT("$a"&MATCH(MAX(C2:C11),C1:C10,0))
to find the maximum in the column and return the time, and the same for the minimum (replacing MAX with MIN above). This will work for "Genus 1" above, but not for the other two, so I have to go through manually and set the first cell with data in each column to a high value (1000), and the last to some low value (-1000). I don't care about the value of each interval, just the number of intervals between the first and last observation. Is there a better way of formulating this?
Hi
I am trying to sum a series as in the Sigma mathematical operation
I have seen this useful thread which gave me ideas http://www.mrexcel.com/forum/showthread.php?t=91030
but I'm still having problems. In that thread the user wanted
20,000*1.035^1
+ 20,000*1.035^2
+ 20,000*1.035^3
+ 20,000*1.035^4
and of the solutions put forward, my favourite was
=SUMPRODUCT(A1*(B1^ROW(INDIRECT(C1&":"&D1))))
Whe
A1 = 20,000
B1 = 1.035
C1 = 1
D1 = 4
The difference with what I'm trying to do is the number 20,000 isn't the number I want to start off the multiplication with each time.
For example I want
20,000*1.035^1
+ 17,000*1.035^2
+ 7,000*1.035^3
+ 2,000*1.035^4
I would have thought I could just replace A1 in the formula with A1:A4 but that returns an error message.
Also I don't get why if the following 2 formulas work
=SUMPRODUCT(G36:G40,H36:H40)
=SUMPRODUCT({1,2,3,4,5},{1000,800,400,300,150})
Then why does = SUMPRODUCT(G36:G40,{1000,800,400,300,150}) return #Value
Would someone be able to help please?
Thanks
Hi,
In a column I have a list with different characters in an order I don't know.
How can I find out how many different characters I have?
In the attached example the result should be 3 (There are A's, B's and C's. But no D's, no E's and so on).
Thanks for your help,
alexandre
Is there a way to count the number of times that a specific character occurs within a range of cells? For example, if I have 20 cells, and I am trying to count the number of times that "X" shows up. There will be times that there will be more than one X per cell. (i.e. A1 = X1X2, B1 = BX, C1 = y, etc. would return "3"). Any help or ideas? Thanks.
Hello;
1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43
Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...
2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x
Your help would be greatly appreciated.
Regards.
Morning!
I have a problem with using wildcard characters in array formula.
I need to COUNTIF according to two criteria and so had been using
=SUM((Data!$C$1:$C$3693="P")*(Data!$E$1:$E$3693="HUTCF"))
Which returns a count of all the cases that match both P and HUTCF which is great!
However HUTCF could also occur at any point in a string of text in the cell, such as HSFBL;HUTCF;HSFG so I thought I could use wild card characters as you would with COUNTIF for just the one critera
=COUNTIF(Data!$E$1:$E$3693,"*HUTCF*"))
like this
=SUM((Data!$C$1:$C$3693="P")*(Data!$E$1:$E$3693="*HUTCF*"))
But its not happy with that and just returns 0s!
Anyone got any ideas!!??
Thanks in advance!
Cath
Similar question to one asked recently:
I would also like to be able to find the maximum consecutive occurrences of "." for each person when results are combined with previous entries. For example, Tom's first result finishes with 4 consecutive "." When combined with his second result which begins with one "." before an "x" the formula result should be 5. (see below)
One person may have 100 different entries. I just need the single maximum consecutive occurrences of "." for each individual when spread across different rows.
John .xxxx
Mary ..x...
Lisa ...xx
Tom .x....
John x.....
Tom .x.xx.
Lisa x.x..x
Mary ......
Thanks
How to create a number series within an array formula, I mean, the series starts with 1 and will finish with 8 for example;
{1;2;3;4;5;6;7;8} - It will be the result within my array formula.
In Aij matrix means i=1 to 8 {i+1;i+1;i+1;i+1;i+1;i+1;i+1;i+1}.
I need to retrieve it within an array formula.
How can I do that just using an array formula.
Luthius
