Excel Formula

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

Hi

I'm hoping someone can help me with this formula I need. I need to look up
where cell D1 falls between columns A and B and give me the answer in column
C in column E..is this even possible? Thanks for your help

A B C D E

1 10 14.9 200 16 300
2 15 19.9 300 14.9 200
3 20 24.9 400 22 400
 
Hi,
I type your example and I tested the formula, are your values being typed or
imported from another source?, check if you don't have blank spaces or do the
exercise I did manually copying the numbers
 
Hi Eduardo

the numbers are manually typed. You put this formula in cells E1, E2 and E3
to get the 300, 200 and 400 answers
 
Hi,
I started in cell E2 if you start in E1 just change the range as follow

=SUMPRODUCT(--(D1>=$A$1:$A$9),--(D1<=$B$1:$B$9),$C$1:$C$9)
 
Hi Eduardo

Its too bad I can't upload the spreadsheet but this is a copy/paste of what
I have...where the "0" is I have the formula as you have....but it should
come back as 200, 300, 400 just like column c


10 14.9 200 16 0
15 19.9 300 14.9 0
20 24.9 400 22 0

=SUMPRODUCT(--(D1>=$A$1:$A$9),--(D1<=$B$1:$B$9),$C$1:$C$9)
 
Dee said:
Its too bad I can't upload the spreadsheet
Click to expand...

You could use Google Docs to upload and share the Excel file.


Dee wrote previouslly:
this formula is giving me a "0" answer?? [....]
=SUMPRODUCT(--(D2>=$A$2:$A$9),
--(D2<=$B$2:$B$9),$C$2:$C$9)
Click to expand...

I wonder if some of the "numbers" are actually text. In comparisons of the
form text<=number, the result is always FALSE. And if any of C2:C29 is text,
SUMPRODUCT treats them as zero.

First, look at the format of the cells. If it was Text before you entered
the numbers, they will be entered as text. If the cell has a numeric format
(General, Number, etc), the content might still be text. Use a formula like
=ISNUMBER(A2) to be sure.


----- original message -----
 
Thanks Joe

That's what is was...the text/number problem...thank you so much

Joe User said:
Dee said:
Its too bad I can't upload the spreadsheet
Click to expand...

You could use Google Docs to upload and share the Excel file.


Dee wrote previouslly:
this formula is giving me a "0" answer?? [....]
=SUMPRODUCT(--(D2>=$A$2:$A$9),
--(D2<=$B$2:$B$9),$C$2:$C$9)
Click to expand...

I wonder if some of the "numbers" are actually text. In comparisons of the
form text<=number, the result is always FALSE. And if any of C2:C29 is text,
SUMPRODUCT treats them as zero.

First, look at the format of the cells. If it was Text before you entered
the numbers, they will be entered as text. If the cell has a numeric format
(General, Number, etc), the content might still be text. Use a formula like
=ISNUMBER(A2) to be sure.


----- original message -----

Dee said:
Hi Eduardo

Its too bad I can't upload the spreadsheet but this is a copy/paste of what
I have...where the "0" is I have the formula as you have....but it should
come back as 200, 300, 400 just like column c


10 14.9 200 16 0
15 19.9 300 14.9 0
20 24.9 400 22 0

=SUMPRODUCT(--(D1>=$A$1:$A$9),--(D1<=$B$1:$B$9),$C$1:$C$9)
Click to expand...
Click to expand...
 
Back
Top