Problem with progressive filling of cells?

  • Thread starter Thread starter qwerty
  • Start date Start date
Q

qwerty

A1=1
A2=A1+0.01
A3=A2+0.01

And so on, for 1400 rows. That data is used by the formula in column
B. The problem is that after row 130 or so the formula in column B
gives weird results. If I go to A130 and input the number with the
keyboard then it works. But only for the next 20 rows or so. Is there
a problem in Excel with a progressive filling like this?
 
What is the formula in B and what are "weird results"?
There is no Excel problem with filling like this.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| A1=1
| A2=A1+0.01
| A3=A2+0.01
|
| And so on, for 1400 rows. That data is used by the formula in column
| B. The problem is that after row 130 or so the formula in column B
| gives weird results. If I go to A130 and input the number with the
| keyboard then it works. But only for the next 20 rows or so. Is there
| a problem in Excel with a progressive filling like this?
 
What is the formula in B and what are "weird results"?
There is no Excel problem with filling like this.
Click to expand...

The formula in B is

=COUNTIF(Data.$I$21:Data.$I$610;"="&A1)

Which counts the occurences of the value in A column in another
sheet. After row 130 or so it gives zeros until the end. If I go to
A130 and type the same value myself it gives different results.
 
It is a bit difficult to try and help but not getting precise information.

<Data.$I$21>

Do you happen to mean:

Data!$I$21
?
Please copy and paste your formula, do not re-type it.
Since you use ";" as a list separator, you seem to have a localized version of Excel. Are there any other symbols differing from
the "International" ones?

<If I go to A130 and type the same value>

What exactly do you type where in which sheet?

<gives different results>

You probably know already what I'm going to ask:

What results? different from what?

We're not clairvoyant! Keep trying!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| @TK2MSFTNGP05.phx.gbl:
|
| > What is the formula in B and what are "weird results"?
| > There is no Excel problem with filling like this.
| >
|
| The formula in B is
|
| =COUNTIF(Data.$I$21:Data.$I$610;"="&A1)
|
| Which counts the occurences of the value in A column in another
| sheet. After row 130 or so it gives zeros until the end. If I go to
| A130 and type the same value myself it gives different results.
 
Please copy and paste your formula, do not re-type it.
Since you use ";" as a list separator, you seem to have a
localized version of Excel. Are there any other symbols
differing from the "International" ones?
Click to expand...

They work like that on my computer, I write functions that way and
that's the way they work. If you don't want to help and just point
out typos then ok.
<If I go to A130 and type the same value>

What exactly do you type where in which sheet?
Click to expand...

In the second sheet, the one with the problem (for example A130), I
need to go to the cell and replace

=A129+0.01

with the value, eg. 2.25, which of course is the same value that
was given by the formula.
<gives different results>

You probably know already what I'm going to ask:

What results? different from what?
Click to expand...

It gives the expected results if I type it myself. If I use the
formula to fill them all in quickly it doesn't. Since I don't feel
like filling 1400 rows myself I need the formula to work.
 
Please don't feel offended, but you still haven't answered my questions and you're still not accurate.

Is it
Data.$I$21
or
Data!$I$21
?

If I follow your formulas, A130 is not 2.25, but 2.29
Also, terms like "130 or so" make me insecure about how accurate your other statements are.
"Expected results" yes, what?
Did you check the formula in A130? What did it read?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| |
| > Please copy and paste your formula, do not re-type it.
| > Since you use ";" as a list separator, you seem to have a
| > localized version of Excel. Are there any other symbols
| > differing from the "International" ones?
|
| They work like that on my computer, I write functions that way and
| that's the way they work. If you don't want to help and just point
| out typos then ok.
|
| >
| > <If I go to A130 and type the same value>
| >
| > What exactly do you type where in which sheet?
|
| In the second sheet, the one with the problem (for example A130), I
| need to go to the cell and replace
|
| =A129+0.01
|
| with the value, eg. 2.25, which of course is the same value that
| was given by the formula.
|
| >
| > <gives different results>
| >
| > You probably know already what I'm going to ask:
| >
| > What results? different from what?
| >
|
| It gives the expected results if I type it myself. If I use the
| formula to fill them all in quickly it doesn't. Since I don't feel
| like filling 1400 rows myself I need the formula to work.
|
 
If I follow your formulas, A130 is not 2.25, but 2.29
Also, terms like "130 or so" make me insecure about how accurate
your other statements are. "Expected results" yes, what?
Did you check the formula in A130? What did it read?
Click to expand...

It's Data!$I$21

What does it matter if it's A130 or A150? I'm telling you what's
going on, you understood. Ok, so the value is 2.29 and not 2.25,
there's your point, congratulations.

Expected results are THOSE THAT ARE EXPECTED FROM THE FORMULA, I.E.
THE NUMBER OF TIMES THE VALUE APPEARS IN THE OTHER SHEET, WHICH I
HAVE ALREADY SAID. How clearer can it get?
 
If you format the data on your Data sheet as General, and make the columns wider, do they still show only two decimals?

BTW, no need to shout.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| |
| > If I follow your formulas, A130 is not 2.25, but 2.29
| > Also, terms like "130 or so" make me insecure about how accurate
| > your other statements are. "Expected results" yes, what?
| > Did you check the formula in A130? What did it read?
| >
|
| It's Data!$I$21
|
| What does it matter if it's A130 or A150? I'm telling you what's
| going on, you understood. Ok, so the value is 2.29 and not 2.25,
| there's your point, congratulations.
|
| Expected results are THOSE THAT ARE EXPECTED FROM THE FORMULA, I.E.
| THE NUMBER OF TIMES THE VALUE APPEARS IN THE OTHER SHEET, WHICH I
| HAVE ALREADY SAID. How clearer can it get?
 
If you format the data on your Data sheet as General, and make
the columns wider, do they still show only two decimals?
Click to expand...

Yes they do. The column in question is with data I have typed and I
never type more than two decimals.
 
I could reproduce your problem.You experience the problems of floating point numbers not being able to represent decimal values
accurately.

See http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| |
| > If you format the data on your Data sheet as General, and make
| > the columns wider, do they still show only two decimals?
| >
|
| Yes they do. The column in question is with data I have typed and I
| never type more than two decimals.
 
=ROUND(A1+0.01,2)

in A2 and fill down fixes it for me. You are just running limitations in
the storage of floating point numbers and accumulated errors caused by this
I believe.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Re. Conditional formating of adjacent cells but down a column 0
A data validation question 1
Automatically populating new rows with formuals above 1
Excel VBA - Problem with Closing Print Preview 0
formula 4
Fill down Formula Automatically 2
Excel Copy COUNTIF while retaining reference 7
Find more than a word in a table 3

Back
Top