Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane

Hi Diane,

the INDEX/MATCH formula is bringing data from column B in your
HourlyData sheet, and I would imagine that although the data in this
column looks like numbers it is actually text. So, you will need to
change the data into numeric form in column B. One way to do this is
to first format the cells in column B as General (in case they are set
to Text), and then click on an empty cell somewhere and click <copy,
then highlight column B and Edit | Paste Special | Values (check) |
Add (check) | OK, then click <Esc.

See if that clears it up.

Hope this helps.

Pete

On Oct 12, 4:53 pm, diaare wrote:
Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane

I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive


"diaare" wrote:

Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane

I don't see how this relates to the earlier question, and I'm not sure
how your question relates to your example numbers. Would you like to
add a bit more detail?

Pete

On Oct 13, 2:08 am, thrive wrote:
I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive



"diaare" wrote:
Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane- Hide quoted text -

- Show quoted text -

Pete

Thanks for the response. I checked all of the cells that the formula is
using, and they are all formatted as numbers. I even tried your cut and
repaste method to make sure, but still no change.

Any other ideas?

"Pete_UK" wrote:

Hi Diane,

the INDEX/MATCH formula is bringing data from column B in your
HourlyData sheet, and I would imagine that although the data in this
column looks like numbers it is actually text. So, you will need to
change the data into numeric form in column B. One way to do this is
to first format the cells in column B as General (in case they are set
to Text), and then click on an empty cell somewhere and click <copy,
then highlight column B and Edit | Paste Special | Values (check) |
Add (check) | OK, then click <Esc.

See if that clears it up.

Hope this helps.

Pete

On Oct 12, 4:53 pm, diaare wrote:
Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane

Not sure how it relates to my question, but here is my best guess at
answering your question (Pete is right though, more details would be helpful):

Number you are testing is in a2
Number you want a2 to be greater than is in cell a3
Number you want a2 to be less than is in cell a4
cell the result is displayed in A5

In cell A5 paste this formula =IF((A2A3)*AND(A2<A4),A3," ") So if a2 is
greater than a3, and less than a4, then a5 will display the number in a3. If
not then a5 will be left blank.

"thrive" wrote:

I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive


"diaare" wrote:

Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane

I realize this does not relate to your original question but I could not
start a new post so I added to this one. Anyway here is more detail for you.
I have the following data
$300,000 $300,000
$359,700 $359,700
$331,643 $359,700
$339,271 $359,700
$377,270 $377,270
$460,646 $460,646
$433,007 $460,646
Column a is a value that is calculated. I need a formula for column 2 that
does a couple of things. 1) The value in column 2 can never be lower than
the 1st number in column 1. 2) If the number in column 2 is greater than the
previous number than the new number in column 2 will be the greater number.
3) If the number in column 1 goes below the previous number than the higher
number of any of the previous numbers becomes the value of column 2. I hope
this is enough information coupled with the above example.





--
thrive


"diaare" wrote:

Not sure how it relates to my question, but here is my best guess at
answering your question (Pete is right though, more details would be helpful):

Number you are testing is in a2
Number you want a2 to be greater than is in cell a3
Number you want a2 to be less than is in cell a4
cell the result is displayed in A5

In cell A5 paste this formula =IF((A2A3)*AND(A2<A4),A3," ") So if a2 is
greater than a3, and less than a4, then a5 will display the number in a3. If
not then a5 will be left blank.

"thrive" wrote:

I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive


"diaare" wrote:

Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane

Okay, assuming your data starts in A2 because of a header in row 1,
put this formula in B2:

=MAX(A2,B1)

and copy down for as many rows as you need.

Hope this helps.

Pete

On Oct 15, 11:00 pm, thrive wrote:
I realize this does not relate to your original question but I could not
start a new post so I added to this one. Anyway here is more detail for you.
I have the following data
$300,000 $300,000
$359,700 $359,700
$331,643 $359,700
$339,271 $359,700
$377,270 $377,270
$460,646 $460,646
$433,007 $460,646
Column a is a value that is calculated. I need a formula for column 2 that
does a couple of things. 1) The value in column 2 can never be lower than
the 1st number in column 1. 2) If the number in column 2 is greater than the
previous number than the new number in column 2 will be the greater number.
3) If the number in column 1 goes below the previous number than the higher
number of any of the previous numbers becomes the value of column 2. I hope
this is enough information coupled with the above example.

--
thrive



"diaare" wrote:
Not sure how it relates to my question, but here is my best guess at
answering your question (Pete is right though, more details would be helpful):

Number you are testing is in a2
Number you want a2 to be greater than is in cell a3
Number you want a2 to be less than is in cell a4
cell the result is displayed in A5

In cell A5 paste this formula =IF((A2A3)*AND(A2<A4),A3," ") So if a2 is
greater than a3, and less than a4, then a5 will display the number in a3. If
not then a5 will be left blank.

"thrive" wrote:

I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive

"diaare" wrote:

Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane- Hide quoted text -

- Show quoted text -

Another suggestion is to put these formulae in a couple of blank cells
somewhere :

=ISTEXT(BQ17)

and

=ISNUMBER(BQ17)

If it really is a number then you will get FALSE for the first formula
and TRUE for the second formula, and vice versa for text which looks
like a number. If it is returning text, then you can multiply your
INDEX/MATCH formula by 1 (or add zero) to turn it into a number.

Hope this helps.

Pete


On Oct 15, 2:45 pm, diaare wrote:
Pete

Thanks for the response. I checked all of the cells that the formula is
using, and they are all formatted as numbers. I even tried your cut and
repaste method to make sure, but still no change.

Any other ideas?



"Pete_UK" wrote:
Hi Diane,

the INDEX/MATCH formula is bringing data from column B in your
HourlyData sheet, and I would imagine that although the data in this
column looks like numbers it is actually text. So, you will need to
change the data into numeric form in column B. One way to do this is
to first format the cells in column B as General (in case they are set
to Text), and then click on an empty cell somewhere and click <copy,
then highlight column B and Edit | Paste Special | Values (check) |
Add (check) | OK, then click <Esc.

See if that clears it up.

Hope this helps.

Pete

On Oct 12, 4:53 pm, diaare wrote:
Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane- Hide quoted text -

- Show quoted text -

Worked great! For future reference what did this formula do? I have never
used max before.
--
thrive


"Pete_UK" wrote:

Okay, assuming your data starts in A2 because of a header in row 1,
put this formula in B2:

=MAX(A2,B1)

and copy down for as many rows as you need.

Hope this helps.

Pete

On Oct 15, 11:00 pm, thrive wrote:
I realize this does not relate to your original question but I could not
start a new post so I added to this one. Anyway here is more detail for you.
I have the following data
$300,000 $300,000
$359,700 $359,700
$331,643 $359,700
$339,271 $359,700
$377,270 $377,270
$460,646 $460,646
$433,007 $460,646
Column a is a value that is calculated. I need a formula for column 2 that
does a couple of things. 1) The value in column 2 can never be lower than
the 1st number in column 1. 2) If the number in column 2 is greater than the
previous number than the new number in column 2 will be the greater number.
3) If the number in column 1 goes below the previous number than the higher
number of any of the previous numbers becomes the value of column 2. I hope
this is enough information coupled with the above example.

--
thrive



"diaare" wrote:
Not sure how it relates to my question, but here is my best guess at
answering your question (Pete is right though, more details would be helpful):

Number you are testing is in a2
Number you want a2 to be greater than is in cell a3
Number you want a2 to be less than is in cell a4
cell the result is displayed in A5

In cell A5 paste this formula =IF((A2A3)*AND(A2<A4),A3," ") So if a2 is
greater than a3, and less than a4, then a5 will display the number in a3. If
not then a5 will be left blank.

"thrive" wrote:

I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive

"diaare" wrote:

Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane- Hide quoted text -

- Show quoted text -

Thanks for feeding back - I'm glad it worked for you.

I could have used an IF statement, along the lines of ... "If one
number is bigger than another then take the first, otherwise take the
second", and this is exactly what the formula does but without the IF.

MAX takes the largest value of the cells specified as parameters, in
this case A2 or B1, in the same way that:

=IF(A2B1, A2, B1)

would do. The MAX function can take more cells than just two, and can
take ranges of cells, but it will always return the biggest number.

Hope this helps.

Pete

On Oct 15, 11:41 pm, thrive wrote:
Worked great! For future reference what did this formula do? I have never
used max before.
--
thrive



"Pete_UK" wrote:
Okay, assuming your data starts in A2 because of a header in row 1,
put this formula in B2:

=MAX(A2,B1)

and copy down for as many rows as you need.

Hope this helps.

Pete

On Oct 15, 11:00 pm, thrive wrote:
I realize this does not relate to your original question but I could not
start a new post so I added to this one. Anyway here is more detail for you.
I have the following data
$300,000 $300,000
$359,700 $359,700
$331,643 $359,700
$339,271 $359,700
$377,270 $377,270
$460,646 $460,646
$433,007 $460,646
Column a is a value that is calculated. I need a formula for column 2 that
does a couple of things. 1) The value in column 2 can never be lower than
the 1st number in column 1. 2) If the number in column 2 is greater than the
previous number than the new number in column 2 will be the greater number.
3) If the number in column 1 goes below the previous number than the higher
number of any of the previous numbers becomes the value of column 2. I hope
this is enough information coupled with the above example.

--
thrive

"diaare" wrote:
Not sure how it relates to my question, but here is my best guess at
answering your question (Pete is right though, more details would be helpful):

Number you are testing is in a2
Number you want a2 to be greater than is in cell a3
Number you want a2 to be less than is in cell a4
cell the result is displayed in A5

In cell A5 paste this formula =IF((A2A3)*AND(A2<A4),A3," ") So if a2 is
greater than a3, and less than a4, then a5 will display the number in a3. If
not then a5 will be left blank.

"thrive" wrote:

I am looking for some help. I have a column of numbers lets say
3
4
5
4
6
I know how to do a formula to find out if a number is greater than another
but how do I do a formula to ask if a cell is greater than another but not
less than another and then I also need to have another cell = the greater
amount??
--
thrive

"diaare" wrote:

Could someone please help me figure out what is going on here?

I have the following in a spreadsheet:

Cell BQ16 = 536 - a raw number
Cell BQ17 = 509 - correctly derived from the formula:
(=INDEX(HourlyData!$A$2:$Z$355,MATCH('Efficiency July-Dec
07'!BQ$3,ManpowerData!$A$2:$A$355,0),2)

In Cell BQ18 I have the formula =BQ17/BQ16 formatted as a percent - should
be 95% but it calculates 0%.

When I place this formula in BQ18 =BQ16/BQ17 I get the #DIV/0! error. Why?
I see 509 in BQ17, not zero. Why does excel think it is a zero?

Am I not able to do a calculation using a number in a cell generated by an
index match function? Or is this something else?

Thanks,
Diane- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -