| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem viewing back the "Unfiltered range" | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
| Range("cell").Select Problem | Excel Discussion (Misc queries) | |||
Linear Mode
