Remember Me?

#1
May 19th 05, 06:06 AM
 Micayla Bergen Posts: n/a
formula not working

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

#2
May 19th 05, 01:29 PM
 Dave Peterson Posts: n/a

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson
#3
May 19th 05, 11:51 PM
 Micayla Bergen Posts: n/a

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

#4
May 20th 05, 12:36 AM
 Dave Peterson Posts: n/a

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson
#5
May 20th 05, 12:49 AM
 Micayla Bergen Posts: n/a

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself

"Dave Peterson" wrote:

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson

#6
May 20th 05, 01:32 AM
 Dave Peterson Posts: n/a

How about the sum(i12,j12) suggestion--do you get an error then?

Micayla Bergen wrote:

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself

"Dave Peterson" wrote:

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson
#7
May 20th 05, 01:41 AM
 Micayla Bergen Posts: n/a

yes

"Dave Peterson" wrote:

How about the sum(i12,j12) suggestion--do you get an error then?

Micayla Bergen wrote:

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself

"Dave Peterson" wrote:

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

#8
May 20th 05, 01:49 AM
 Dave Peterson Posts: n/a

What's in I12?
What's in J12?

Micayla Bergen wrote:

yes

"Dave Peterson" wrote:

How about the sum(i12,j12) suggestion--do you get an error then?

Micayla Bergen wrote:

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself

"Dave Peterson" wrote:

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson
#9
May 20th 05, 02:01 AM
 Micayla Bergen Posts: n/a

I12is =IF(G4=0,H4*D4, "")
J12 is =IF(G4=1,H4*D4, "")
G4 is =IF(ISERROR((VLOOKUP(A4,'Model Portfolio'!\$C\$6:\$C\$50,1, FALSE))),0,1)
H4 is a vlookup
D4 is a manually input number

"Dave Peterson" wrote:

What's in I12?
What's in J12?

Micayla Bergen wrote:

yes

"Dave Peterson" wrote:

How about the sum(i12,j12) suggestion--do you get an error then?

Micayla Bergen wrote:

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself

"Dave Peterson" wrote:

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

#10
May 20th 05, 02:08 AM
 Dave Peterson Posts: n/a

What does I12 evaluate to?
What does J12 evaluate to?

Micayla Bergen wrote:

I12is =IF(G4=0,H4*D4, "")
J12 is =IF(G4=1,H4*D4, "")
G4 is =IF(ISERROR((VLOOKUP(A4,'Model Portfolio'!\$C\$6:\$C\$50,1, FALSE))),0,1)
H4 is a vlookup
D4 is a manually input number

"Dave Peterson" wrote:

What's in I12?
What's in J12?

Micayla Bergen wrote:

yes

"Dave Peterson" wrote:

How about the sum(i12,j12) suggestion--do you get an error then?

Micayla Bergen wrote:

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself

"Dave Peterson" wrote:

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.

Micayla Bergen wrote:

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.

"Dave Peterson" wrote:

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.

Micayla Bergen wrote:

= SUM('A & E John Super'!\$P\$4:\$P\$39)/SUM('A & E John Super'!\$L\$4:\$Q\$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!\$D\$2:\$D\$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM JJL Excel Discussion (Misc queries) 0 March 11th 05 05:11 PM Thrava Excel Discussion (Misc queries) 5 March 6th 05 10:18 PM Carl Hilton Excel Worksheet Functions 13 January 9th 05 07:55 PM Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM

All times are GMT +1. The time now is 05:46 AM.