Problem with "IF" in a range
 

Good Day.

I'm trying to create a formula for a range that says if Column A equals my
criteria, do a calculation on the same row in column D. I can do it with one
cell:

=IF(A10="Allen, Emily.Jun
02",IF((D10*1440)-4650,"",((D10*1440)-465)*-1),999) [where 999=error]

But, when I go to a range:

{=IF(A8:A1000="Allen, Emily.Jun
02",IF((D8:D1000*1440)-4650,"",((D8:D1000*1440)-465)*-1),999)}
[committed with Ctrl+Shift+Enter]

it results with "999"

I think it might be a formatting issue - both columns are formatted
"General" as they come as a data dump out of our phone system.

Any ideas?

Thanks,
Greg

Problem with "IF" in a range
 

This doesn't contain an error check, but I believe it return the value you
are looking for (or 0, if not matches found).

=SUMPRODUCT(--(A8:A10="Allen, Emily.Jun
02"),--((D8:D10)*1440-4650),((D8:D10)*1440-465)*-1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

Good Day.

I'm trying to create a formula for a range that says if Column A equals my
criteria, do a calculation on the same row in column D. I can do it with one
cell:

=IF(A10="Allen, Emily.Jun
02",IF((D10*1440)-4650,"",((D10*1440)-465)*-1),999) [where 999=error]

But, when I go to a range:

{=IF(A8:A1000="Allen, Emily.Jun
02",IF((D8:D1000*1440)-4650,"",((D8:D1000*1440)-465)*-1),999)}
[committed with Ctrl+Shift+Enter]

it results with "999"

I think it might be a formatting issue - both columns are formatted
"General" as they come as a data dump out of our phone system.

Any ideas?

Thanks,
Greg

Problem with "IF" in a range
 

I get a "#VALUE! error.

Any idea?

Thanks,

Greg

"Luke M" wrote:

This doesn't contain an error check, but I believe it return the value you
are looking for (or 0, if not matches found).

=SUMPRODUCT(--(A8:A10="Allen, Emily.Jun
02"),--((D8:D10)*1440-4650),((D8:D10)*1440-465)*-1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

Good Day.

I'm trying to create a formula for a range that says if Column A equals my
criteria, do a calculation on the same row in column D. I can do it with one
cell:

=IF(A10="Allen, Emily.Jun
02",IF((D10*1440)-4650,"",((D10*1440)-465)*-1),999) [where 999=error]

But, when I go to a range:

{=IF(A8:A1000="Allen, Emily.Jun
02",IF((D8:D1000*1440)-4650,"",((D8:D1000*1440)-465)*-1),999)}
[committed with Ctrl+Shift+Enter]

it results with "999"

I think it might be a formatting issue - both columns are formatted
"General" as they come as a data dump out of our phone system.

Any ideas?

Thanks,
Greg

Problem with "IF" in a range
 

Make sure all range sizes are the same. Also, make sure all the data in
D8:D1000 is actaully numbers, and not text. (multiplying text causes errors)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

I get a "#VALUE! error.

Any idea?

Thanks,

Greg

"Luke M" wrote:

This doesn't contain an error check, but I believe it return the value you
are looking for (or 0, if not matches found).

=SUMPRODUCT(--(A8:A10="Allen, Emily.Jun
02"),--((D8:D10)*1440-4650),((D8:D10)*1440-465)*-1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

Good Day.

I'm trying to create a formula for a range that says if Column A equals my
criteria, do a calculation on the same row in column D. I can do it with one
cell:

=IF(A10="Allen, Emily.Jun
02",IF((D10*1440)-4650,"",((D10*1440)-465)*-1),999) [where 999=error]

But, when I go to a range:

{=IF(A8:A1000="Allen, Emily.Jun
02",IF((D8:D1000*1440)-4650,"",((D8:D1000*1440)-465)*-1),999)}
[committed with Ctrl+Shift+Enter]

it results with "999"

I think it might be a formatting issue - both columns are formatted
"General" as they come as a data dump out of our phone system.

Any ideas?

Thanks,
Greg

Problem with "IF" in a range
 

"Luke M" wrote:

Make sure all range sizes are the same. Also, make sure all the data in
D8:D1000 is actaully numbers, and not text. (multiplying text causes errors)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

I get a "#VALUE! error.

Any idea?

Thanks,

Greg

"Luke M" wrote:

This doesn't contain an error check, but I believe it return the value you
are looking for (or 0, if not matches found).

=SUMPRODUCT(--(A8:A10="Allen, Emily.Jun
02"),--((D8:D10)*1440-4650),((D8:D10)*1440-465)*-1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

Good Day.

I'm trying to create a formula for a range that says if Column A equals my
criteria, do a calculation on the same row in column D. I can do it with one
cell:

=IF(A10="Allen, Emily.Jun
02",IF((D10*1440)-4650,"",((D10*1440)-465)*-1),999) [where 999=error]

But, when I go to a range:

{=IF(A8:A1000="Allen, Emily.Jun
02",IF((D8:D1000*1440)-4650,"",((D8:D1000*1440)-465)*-1),999)}
[committed with Ctrl+Shift+Enter]

it results with "999"

I think it might be a formatting issue - both columns are formatted
"General" as they come as a data dump out of our phone system.

Any ideas?

Thanks,
Greg

Problem with "IF" in a range
 

First, there are three dots in that value (...) unless you've changed something.

Second, using =vlookup() may work better. See your previous thread.

Greg wrote:

Good Day.

I'm trying to create a formula for a range that says if Column A equals my
criteria, do a calculation on the same row in column D. I can do it with one
cell:

=IF(A10="Allen, Emily.Jun
02",IF((D10*1440)-4650,"",((D10*1440)-465)*-1),999) [where 999=error]

But, when I go to a range:

{=IF(A8:A1000="Allen, Emily.Jun
02",IF((D8:D1000*1440)-4650,"",((D8:D1000*1440)-465)*-1),999)}
[committed with Ctrl+Shift+Enter]

it results with "999"

I think it might be a formatting issue - both columns are formatted
"General" as they come as a data dump out of our phone system.

Any ideas?

Thanks,
Greg

--

Dave Peterson