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)

The "merging" of formulas is logical only in you intend for one formula to
use the result of another formula in its calculations. Otherwise it doesn't
make sense to do so. HTH Otto
wrote in message
...
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)

On Feb 25, 10:20 am, "Otto Moehrbach"
wrote:
The "merging" of formulas is logical only in you intend for one formula to
use the result of another formula in its calculations. Otherwise it doesn't
make sense to do so. HTH wrote in message


That's exactly what I'm looking for. So is it possible to do?

The "merging" of formulas is logical only in you intend for one formula
to
use the result of another formula in its calculations. Otherwise it
doesn't
make sense to do so. HTH wrote in message

That's exactly what I'm looking for. So is it possible to do?

Did you try looking in the other newsgroup you posted this question to?

Rick

On Feb 25, 1:26 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
The "merging" of formulas is logical only in you intend for one formula
to
use the result of another formula in its calculations. Otherwise it
doesn't
make sense to do so. HTH wrote in message

That's exactly what I'm looking for. So is it possible to do?

Did you try looking in the other newsgroup you posted this question to?

Rick

I just checked it out, thanks.

I'm just looking for a simple "Yes, you can; right click on the cell
and choose 'merge formulas'..." or "No, you can't, but what is it that
you're trying to do in the first place? Why don't you just..." So far
nobody has given me a yes or no answer.

wrote in message
...
On Feb 25, 1:26 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
The "merging" of formulas is logical only in you intend for one
formula
to
use the result of another formula in its calculations. Otherwise it
doesn't
make sense to do so. HTH wrote in message

That's exactly what I'm looking for. So is it possible to do?

Did you try looking in the other newsgroup you posted this question to?

Rick

I just checked it out, thanks.

I'm just looking for a simple "Yes, you can; right click on the cell
and choose 'merge formulas'..." or "No, you can't, but what is it that
you're trying to do in the first place? Why don't you just..." So far
nobody has given me a yes or no answer.

No, there is no "merge formula" feature.

You've said that the formula works on all but one row but you haven't
defined what "doesn't work" means.

--
Biff
Microsoft Excel MVP

On Feb 25, 11:00 pm, "T. Valko" wrote:

No, there is no "merge formula" feature.

You've said that the formula works on all but one row but you haven't
defined what "doesn't work" means.

Thanks tremendously for clearing that up for me, Biff.

The formula references a range of cells, and it is supposed to rank
the values of that range. The RANK() function will duplicate rank
numbers for duplicate values. I.e., if there are two $5.95s, for
example, they will both be ranked 4, and the next number will be
ranked 6. That's where the COUNTIF() part comes in -- it counts the
instances of that value in the range, and adds the number of instances
to the rank. So if there are two $5.95s, and they are both ranked 4,
the first one is +0, which then has a rank of 4, and the second one is
+1, which then has a rank of 5. The next value already has a rank of 6
( and gets a +0 ), so the world is good.

I've used the formula in several places and it ranked all the values
without duplicating values, except for in one place. While going over
the values, I noticed that one range had two 11s. So that's what I
mean when I say that the formula 'doesn't work'. It is supposed to
give a serial ranking of values, with no duplication of ranking
numbers. However, in this one spot, it was giving me a duplication.

So, to troubleshoot, I split the formula into two parts, one in each
cell.
Column U
=COUNTIF(Q$10:$Q10,$Q10 )-1

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

Then, I added the two values of the cells together, which should give
me the same values of the whole formula in a single cell:

Column X
=U10+W10

So, theoretically, column X should have the same value as the complete
formula, right?
COUNTIF(Q$10:$Q10,$Q10 )-1 + RANK($Q10,$Q$10:$Q$29)

It should work, right? But it doesn't. They are logically identical,
right? They should be the same, shouldn't they? But yet, they still
disagree.

However, there is an upside. Column X gives me the expected range of
values -- the ranking of another range with no duplicates. In other
words, column X 'works' -- it does what I want it to do -- what I
expect it to do. What it's 'supposed' to do.

So now I have the right values, but I have a formatting problem. In
other areas of the spreadsheet, I have a formula in a single cell to
rank values. In this problematic section, I have to have three columns
to get the proper value. I've spent more time than I should have
trying to fix this bug, so i just need it to be over and done with.
Since column X has the proper value, what would be ideal is to just
merge the formulas in column X, and hopefully get the correct ranking
in a single column like the rest of the spreadsheet. What I was
looking for was a simple "Yes, you can merge formulas" or "No, you
can't merge formulas".