merge formulas?

[lawpoop]

Hello all -

I have a particularly tricky function that I'm troubleshooting by
splitting the parts into different cells. I have the various cells
adding up correctly, but still i have a typo or some problem in the
original cell formula.

Is there a way I can merge the formulas in cells? For instance, if I
have

Column U
=COUNTIF(Q$10:$Q10,$Q10 )-1

Column W
=RANK($Q10,$Q$10:$Q$29)

Column X
=U10+W10

I would like for column X to become:

COUNTIF(Q$10:$Q10,$Q10 )-1 + RANK($Q10,$Q$10:$Q$29)

 

[Rick Rothstein \(MVP - VB\)]

I have a particularly tricky function that I'm troubleshooting by

splitting the parts into different cells. I have the various cells
adding up correctly, but still i have a typo or some problem in the
original cell formula.

Is there a way I can merge the formulas in cells? For instance, if I
have

Column U
=COUNTIF(Q$10:$Q10,$Q10 )-1

Column W
=RANK($Q10,$Q$10:$Q$29)

Column X
=U10+W10

I would like for column X to become:

COUNTIF(Q$10:$Q10,$Q10 )-1 + RANK($Q10,$Q$10:$Q$29)

Just use what you wrote, but put an equal sign (=) in front of it so Excel
will read it as a formula.

Rick

 

[lawpoop]

Just use what you wrote, but put an equal sign (=) in front of it soExcel
will read it as a formula.

Rick

Are you advising me simply to enter:
= COUNTIF(Q$10:$Q10,$Q10 )-1 + RANK($Q10,$Q$10:$Q$29)
?

Rick, thanks for the help, but that's exactly what I'm doing, and it's
not working. Obviously, there's some problem somewhere; a typo or
something. It works in 40 other cells, but in one cell, it messes up.
Right now all I want to know if there is some kind of 'merge formulas'
functionality ( regardless of what it's called ).

Originally, I began with this longer formula, and it kept giving me
incorrect values for a certain set of rows. To track down where
exactly the error was occurring, I split the two major parts up into
separate cells, and then added them together. The formula works when
split up into two cells and added together -- don't ask me why, I've
wasted too much time on this already. So rather than waste more time
trying to hunt down my little bug, it would be easier to 'merge' the
formulas and get on with things.

I even re-created the problematic rows in lower down in the
spreadsheet, and they give me the same problem. So, so far, my two
problem-identification strategies ( 1. break the problem down into
parts, and 2. re-create the thing to see if you can see where the bug
originates ) have both failed. I'm just trying to find an easy way out
at this point.

 

[Niek Otten]

<it messes up>

What does that mean?

Please give the values of the input cells and what exactly is the result of your formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| On Feb 25, 10:25 am, "Rick Rothstein \(MVP - VB\)"
| > > I have a particularly tricky function that I'm troubleshooting by
| > > splitting the parts into different cells. I have the various cells
| > > adding up correctly, but still i have a typo or some problem in the
| > > original cell formula.
| >
| > > Is there a way I can merge the formulas in cells? For instance, if I
| > > have
| >
| > > Column U
| > > =COUNTIF(Q$10:$Q10,$Q10 )-1
| >
| > > Column W
| > > =RANK($Q10,$Q$10:$Q$29)
| >
| > > Column X
| > > =U10+W10
| >
| > > I would like for column X to become:
| >
| > > COUNTIF(Q$10:$Q10,$Q10 )-1 + RANK($Q10,$Q$10:$Q$29)
| >
| > Just use what you wrote, but put an equal sign (=) in front of it soExcel
| > will read it as a formula.
| >
| > Rick
|
| Are you advising me simply to enter:
| = COUNTIF(Q$10:$Q10,$Q10 )-1 + RANK($Q10,$Q$10:$Q$29)
| ?
|
| Rick, thanks for the help, but that's exactly what I'm doing, and it's
| not working. Obviously, there's some problem somewhere; a typo or
| something. It works in 40 other cells, but in one cell, it messes up.
| Right now all I want to know if there is some kind of 'merge formulas'
| functionality ( regardless of what it's called ).
|
| Originally, I began with this longer formula, and it kept giving me
| incorrect values for a certain set of rows. To track down where
| exactly the error was occurring, I split the two major parts up into
| separate cells, and then added them together. The formula works when
| split up into two cells and added together -- don't ask me why, I've
| wasted too much time on this already. So rather than waste more time
| trying to hunt down my little bug, it would be easier to 'merge' the
| formulas and get on with things.
|
| I even re-created the problematic rows in lower down in the
| spreadsheet, and they give me the same problem. So, so far, my two
| problem-identification strategies ( 1. break the problem down into
| parts, and 2. re-create the thing to see if you can see where the bug
| originates ) have both failed. I'm just trying to find an easy way out
| at this point.

 

[lawpoop]

<it messes up>

What does that mean?

Please give the values of the input cells and what exactly is the result of your formula

Hey Neik -

Thanks for taking the time to look into this.

I have a column that's supposed to be a serial ranking of another
column's values. The RANK() function will have duplicate rank numbers
for duplicate values, so the COUNTIF-1 is supposed to correct that.
The final column you'll see here is a rank of the profit:

Menu Item Price Cost Profit Rank
A $12.95 $3.35 $9.60 4
B $7.95 $2.00 $5.95 10
C $17.95 $6.03 $11.92 1
D $11.95 $4.00 $7.95 7
E $14.95 $6.27 $8.68 5
F $9.95 $4.00 $5.95 12
G $16.95 $5.76 $11.19 3
H $10.95 $3.50 $7.45 9
I $14.95 $3.19 $11.76 2
J $9.95 $2.00 $7.95 8
K $11.95 $3.82 $8.13 6
L $7.50 $2.50 $5.00 12

The formula for the profit is simple subtraction:
=IF(C10=0,"",B10-C10)

So you see there are two '12's in the rank.
This is the formula that I'm using:
=IF(D10 ="","",RANK(D10,$D$10:$D$28,0)+COUNTIF(D$10:$D10,D10 )-1)
This formula worked perfectly -- meaning a correct serial ranking of
the values with no skips or duplications -- in other areas of the
spreadsheet.

( note that actual values start on row 10 of the spreadsheet ).

The COUNTIF is supposed to add an extra value for the number of
instances of a value. Since you don't want to add the total count of
$5.95s in the range, but only the count so far, the range of the
COUNTIF must change throughout the column. So the final formula looks
like this:
=IF(D21 ="","",RANK(D21,$D$10:$D$28,0)+COUNTIF(D$10:$D21,D21 )-1)

So, to troubleshoot, I split the formula into two columns, one for the
RANK() function, and one for the COUNTIF function:
Profit Count Rank Count+Rank
$9.60 0 4 4
$5.95 0 10 10
$11.92 0 1 1
$7.95 0 7 7
$8.68 0 5 5
$5.95 1 11 12
$11.19 0 3 3
$7.45 0 9 9
$11.76 0 2 2
$7.95 1 7 8
$8.13 0 6 6
$5.00 0 12 12

The formula for the count column:
=IF(D10 ="","",COUNTIF(D$10:$D10,D10 )-1
rank:
=RANK($D10,$D$10:$D$28)
count+rank:
=F10+G10

So it looks like the problem is with the RANK function. The RANK has
given the values of 10 and 11 to $5.95 -- yet it gave 7 to both $7.95.
It seems to not work as advertised on the $5.95 values -- the
documentation says that it should give the same rank value to the same
values. I don't know if it's a decimal error, since the profit column
is actually a formula, and perhaps at some precision both of the
$5.95s are not the same.

In any case, this is the best that I can re-create on a fresh
spreadsheet. On my original spreadsheet, I do have a column that ranks
correctly, but it refers two other columns for the complete formula,
which is why I was looking for a merge formulas feature. I need to
have the ranking in a single column, like in other areas of the
spreadsheet.

 

[Niek Otten]

Surely you mean

=IF(D10 ="","",RANK(D10,$D$10:$D$28,0)+COUNTIF(D$10$28,D10 )-1)

Note the range in countif

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| > <it messes up>
| >
| > What does that mean?
| >
| > Please give the values of the input cells and what exactly is the result of your formula
| >
|
| Hey Neik -
|
| Thanks for taking the time to look into this.
|
| I have a column that's supposed to be a serial ranking of another
| column's values. The RANK() function will have duplicate rank numbers
| for duplicate values, so the COUNTIF-1 is supposed to correct that.
| The final column you'll see here is a rank of the profit:
|
| Menu Item Price Cost Profit Rank
| A $12.95 $3.35 $9.60 4
| B $7.95 $2.00 $5.95 10
| C $17.95 $6.03 $11.92 1
| D $11.95 $4.00 $7.95 7
| E $14.95 $6.27 $8.68 5
| F $9.95 $4.00 $5.95 12
| G $16.95 $5.76 $11.19 3
| H $10.95 $3.50 $7.45 9
| I $14.95 $3.19 $11.76 2
| J $9.95 $2.00 $7.95 8
| K $11.95 $3.82 $8.13 6
| L $7.50 $2.50 $5.00 12
|
| The formula for the profit is simple subtraction:
| =IF(C10=0,"",B10-C10)
|
| So you see there are two '12's in the rank.
| This is the formula that I'm using:
| =IF(D10 ="","",RANK(D10,$D$10:$D$28,0)+COUNTIF(D$10:$D10,D10 )-1)
| This formula worked perfectly -- meaning a correct serial ranking of
| the values with no skips or duplications -- in other areas of the
| spreadsheet.
|
| ( note that actual values start on row 10 of the spreadsheet ).
|
| The COUNTIF is supposed to add an extra value for the number of
| instances of a value. Since you don't want to add the total count of
| $5.95s in the range, but only the count so far, the range of the
| COUNTIF must change throughout the column. So the final formula looks
| like this:
| =IF(D21 ="","",RANK(D21,$D$10:$D$28,0)+COUNTIF(D$10:$D21,D21 )-1)
|
| So, to troubleshoot, I split the formula into two columns, one for the
| RANK() function, and one for the COUNTIF function:
| Profit Count Rank Count+Rank
| $9.60 0 4 4
| $5.95 0 10 10
| $11.92 0 1 1
| $7.95 0 7 7
| $8.68 0 5 5
| $5.95 1 11 12
| $11.19 0 3 3
| $7.45 0 9 9
| $11.76 0 2 2
| $7.95 1 7 8
| $8.13 0 6 6
| $5.00 0 12 12
|
| The formula for the count column:
| =IF(D10 ="","",COUNTIF(D$10:$D10,D10 )-1
| rank:
| =RANK($D10,$D$10:$D$28)
| count+rank:
| =F10+G10
|
| So it looks like the problem is with the RANK function. The RANK has
| given the values of 10 and 11 to $5.95 -- yet it gave 7 to both $7.95.
| It seems to not work as advertised on the $5.95 values -- the
| documentation says that it should give the same rank value to the same
| values. I don't know if it's a decimal error, since the profit column
| is actually a formula, and perhaps at some precision both of the
| $5.95s are not the same.
|
| In any case, this is the best that I can re-create on a fresh
| spreadsheet. On my original spreadsheet, I do have a column that ranks
| correctly, but it refers two other columns for the complete formula,
| which is why I was looking for a merge formulas feature. I need to
| have the ranking in a single column, like in other areas of the
| spreadsheet.
|

 

[lawpoop]

Surely you mean

=IF(D10 ="","",RANK(D10,$D$10:$D$28,0)+COUNTIF(D$10$28,D10 )-1)

Note the range in countif

Neik, have you tested this? Does your formula have the entire column
range in the COUNTIF? If so, that's not what you want to do.

As I explained in my earlier post ( perhaps poorly so ), you don't
want to have the entire range in the COUNTIF. You only want up to the
current row in the range.

Let's say there are three $5.95s in the whole range. What you want to
do is have the first one be +0, the second be +1, and the third +2. If
you have the entire range, then each $5.95 would be +2. So what you
want to do is have the range be only up until the current row. That
way, you only count the number of $5.95s up unto the current row. The
first $5.95 would be +0, the second +1, and the third +2.

 

[Niek Otten]

Yep; my mistake, did it too quickly.

BTW I don't get any duplicates!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| > Surely you mean
| >
| > =IF(D10 ="","",RANK(D10,$D$10:$D$28,0)+COUNTIF(D$10$28,D10 )-1)
| >
| > Note the range in countif
|
| Neik, have you tested this? Does your formula have the entire column
| range in the COUNTIF? If so, that's not what you want to do.
|
| As I explained in my earlier post ( perhaps poorly so ), you don't
| want to have the entire range in the COUNTIF. You only want up to the
| current row in the range.
|
| Let's say there are three $5.95s in the whole range. What you want to
| do is have the first one be +0, the second be +1, and the third +2. If
| you have the entire range, then each $5.95 would be +2. So what you
| want to do is have the range be only up until the current row. That
| way, you only count the number of $5.95s up unto the current row. The
| first $5.95 would be +0, the second +1, and the third +2.

 

[lawpoop]

Yep; my mistake, did it too quickly.

BTW I don't get any duplicates!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

: ) I figured as much. Thanks!

 

[jxc]

test

Hey Neik -

Thanks for taking the time to look into this.

I have a column that's supposed to be a serial ranking of another
column's values. The RANK() function will have duplicate rank numbers
for duplicate values, so the COUNTIF-1 is supposed to correct that.
The final column you'll see here is a rank of the profit:

Menu Item Price Cost Profit Rank
A $12.95 $3.35 $9.60 4
B $7.95 $2.00 $5.95 10
C $17.95 $6.03 $11.92 1
D $11.95 $4.00 $7.95 7
E $14.95 $6.27 $8.68 5
F $9.95 $4.00 $5.95 12
G $16.95 $5.76 $11.19 3
H $10.95 $3.50 $7.45 9
I $14.95 $3.19 $11.76 2
J $9.95 $2.00 $7.95 8
K $11.95 $3.82 $8.13 6
L $7.50 $2.50 $5.00 12

The formula for the profit is simple subtraction:
=IF(C10=0,"",B10-C10)

So you see there are two '12's in the rank.
This is the formula that I'm using:
=IF(D10 ="","",RANK(D10,$D$10:$D$28,0)+COUNTIF(D$10:$D10,D10 )-1)
This formula worked perfectly -- meaning a correct serial ranking of
the values with no skips or duplications -- in other areas of the
spreadsheet.

( note that actual values start on row 10 of the spreadsheet ).

The COUNTIF is supposed to add an extra value for the number of
instances of a value. Since you don't want to add the total count of
$5.95s in the range, but only the count so far, the range of the
COUNTIF must change throughout the column. So the final formula looks
like this:
=IF(D21 ="","",RANK(D21,$D$10:$D$28,0)+COUNTIF(D$10:$D21,D21 )-1)

So, to troubleshoot, I split the formula into two columns, one for the
RANK() function, and one for the COUNTIF function:
Profit Count Rank Count+Rank
$9.60 0 4 4
$5.95 0 10 10
$11.92 0 1 1
$7.95 0 7 7
$8.68 0 5 5
$5.95 1 11 12
$11.19 0 3 3
$7.45 0 9 9
$11.76 0 2 2
$7.95 1 7 8
$8.13 0 6 6
$5.00 0 12 12

The formula for the count column:
=IF(D10 ="","",COUNTIF(D$10:$D10,D10 )-1
rank:
=RANK($D10,$D$10:$D$28)
count+rank:
=F10+G10

So it looks like the problem is with the RANK function. The RANK has
given the values of 10 and 11 to $5.95 -- yet it gave 7 to both $7.95.
It seems to not work as advertised on the $5.95 values -- the
documentation says that it should give the same rank value to the same
values. I don't know if it's a decimal error, since the profit column
is actually a formula, and perhaps at some precision both of the
$5.95s are not the same.

In any case, this is the best that I can re-create on a fresh
spreadsheet. On my original spreadsheet, I do have a column that ranks
correctly, but it refers two other columns for the complete formula,
which is why I was looking for a merge formulas feature. I need to
have the ranking in a single column, like in other areas of the
spreadsheet.