Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Hi,
What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Try this:
=IF(J2:O2="","",I2-SUM(J2:O2)) Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Hello,
Thanks for your help with this. I put it in, and got a #VALUE in that cell. Also, I tried adding dollar amounts and it didn't calculate. Sorry for the bother ... Rosemary "Teethless mama" wrote: Try this: =IF(J2:O2="","",I2-SUM(J2:O2)) Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Not sure what the calculation should do but try entering it as an array with
SHIFT+CTRL+ENTER in place of just ENTER best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Rosemary" wrote in message ... Hello, Thanks for your help with this. I put it in, and got a #VALUE in that cell. Also, I tried adding dollar amounts and it didn't calculate. Sorry for the bother ... Rosemary "Teethless mama" wrote: Try this: =IF(J2:O2="","",I2-SUM(J2:O2)) Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
=IF(J2:O2="","",I2-SUM(J2:O2))
ctrlshiftenter (not just enter) "Rosemary" wrote: Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Hello,
I think the problem is in the first J2:O2 reference. I write it this way, referencing only one cell in the range ... =IF(J2="","",I2-SUM(J2:O2)) .... it works. I think I just need to figure out how to write the J2 through O2 reference in the first part of the statement. Thanks! Rosemary "Teethless mama" wrote: Try this: =IF(J2:O2="","",I2-SUM(J2:O2)) Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Great, thanks all. That worked. I have one error in my own logic, however.
I want the Balance Due cell (P2) to calculate if ANY ONE OR MORE of the cells J2 through O2 have amounts entered in them. Right now, the way I wrote the logic, it will only calculate of ALL cells (J2 thru O2) have amounts in them. Sorry ... how would I change the formula? Thanks again, Rosemary "Teethless mama" wrote: =IF(J2:O2="","",I2-SUM(J2:O2)) ctrlshiftenter (not just enter) "Rosemary" wrote: Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
=IF( SUMPRODUCT( LEN( J2:O2 ) ) = 0, "", I2 - SUM( J2:O2 ) )
-- Regards, Luc. "Festina Lente" "Rosemary" wrote: Great, thanks all. That worked. I have one error in my own logic, however. I want the Balance Due cell (P2) to calculate if ANY ONE OR MORE of the cells J2 through O2 have amounts entered in them. Right now, the way I wrote the logic, it will only calculate of ALL cells (J2 thru O2) have amounts in them. Sorry ... how would I change the formula? Thanks again, Rosemary "Teethless mama" wrote: =IF(J2:O2="","",I2-SUM(J2:O2)) ctrlshiftenter (not just enter) "Rosemary" wrote: Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function error
Hi Rosemary
Isn't it simply =IF(COUNT(J2:O)=0,"",I2-SUM(J2:O2)) -- Regards Roger Govier "Rosemary" wrote in message ... Great, thanks all. That worked. I have one error in my own logic, however. I want the Balance Due cell (P2) to calculate if ANY ONE OR MORE of the cells J2 through O2 have amounts entered in them. Right now, the way I wrote the logic, it will only calculate of ALL cells (J2 thru O2) have amounts in them. Sorry ... how would I change the formula? Thanks again, Rosemary "Teethless mama" wrote: =IF(J2:O2="","",I2-SUM(J2:O2)) ctrlshiftenter (not just enter) "Rosemary" wrote: Hi, What is wrong with my formula? Here is the formula I wrote. I placed it in the function field: =SUM(IF(J2:O2=""),"",I2-(J2:O2)) Here is what I am trying to do with the formula -- IF J2 thru O2 are all blank, THEN this cell stays blank ELSE This cell shows the result of I2 minus (sum of J2 thru O2) END IF Cell I2 is amount due, J2 thru O2 are payments made, P2 is balance due. Requestor wants P2 left blank if no payments have been made. If payments have been made, they should be totalled then subtracted from I2, and the results displayed in P2. Thanks! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
How too set last edited date in excel and which user was it? | Excel Worksheet Functions | |||
Function giving error | Excel Discussion (Misc queries) | |||
Function giving Error | Excel Worksheet Functions | |||
Custom function returning VALUE error | Excel Discussion (Misc queries) |