Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
Hi,
=SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
Hi Eduardo
thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
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 "Dee" wrote: Hi Eduardo thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
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 "Eduardo" wrote: 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 "Dee" wrote: Hi Eduardo thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
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) "Dee" wrote: 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 "Eduardo" wrote: 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 "Dee" wrote: Hi Eduardo thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
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) "Eduardo" wrote: 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) "Dee" wrote: 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 "Eduardo" wrote: 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 "Dee" wrote: Hi Eduardo thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
"Dee" wrote:
Its too bad I can't upload the spreadsheet 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) 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" wrote: 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) "Eduardo" wrote: 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) "Dee" wrote: 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 "Eduardo" wrote: 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 "Dee" wrote: Hi Eduardo thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
Thanks Joe
That's what is was...the text/number problem...thank you so much "Joe User" wrote: "Dee" wrote: Its too bad I can't upload the spreadsheet 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) 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" wrote: 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) "Eduardo" wrote: 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) "Dee" wrote: 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 "Eduardo" wrote: 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 "Dee" wrote: Hi Eduardo thanks for you help....however this formula is giving me a "0" answer?? "Eduardo" wrote: Hi, =SUMPRODUCT(--(D2=$A$2:$A$9),--(D2<=$B$2:$B$9),$C$2:$C$9) copy formula down "Dee" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions |