Problem with "IF" in a range

G

Greg

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)-465>0,"",((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)-465>0,"",((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
 
L

Luke M

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-465>0),((D8:D10)*1440-465)*-1)
 
G

Greg

I get a "#VALUE! error.

Any idea?

Thanks,

Greg

Luke M said:
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-465>0),((D8:D10)*1440-465)*-1)
--
Best Regards,

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


Greg said:
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)-465>0,"",((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)-465>0,"",((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
Click to expand...
Click to expand...
 
L

Luke M

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 said:
I get a "#VALUE! error.

Any idea?

Thanks,

Greg

Luke M said:
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-465>0),((D8:D10)*1440-465)*-1)
--
Best Regards,

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


Greg said:
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)-465>0,"",((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)-465>0,"",((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
Click to expand...
Click to expand...
Click to expand...
 
G

Greg

Luke M said:
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 said:
I get a "#VALUE! error.

Any idea?

Thanks,

Greg

Luke M said:
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-465>0),((D8:D10)*1440-465)*-1)
--
Best Regards,

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


:

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)-465>0,"",((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)-465>0,"",((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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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

Second, using =vlookup() may work better. See your previous thread.
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)-465>0,"",((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)-465>0,"",((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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with a formula I am editing 5
Autofill Problem 5
Formula Based on Values in Named Range Q 3
Copying a split range 3
VLOOKUP & IF used with > or < 7
Multiple variables to sort and sum, return values<0 with sum refer 5
Excel VBA - Counting Unique Cells in a Range 2
IF statement with FALSE value being text AND cell reference? 1

Top