Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum If - I think
I have a worksheet with data in Columns D, E, F, G, and H that I will use to
calculate column I. If E2 contains a numeric value, then transfer that value to I2, but if E2 is Blank or NA then use the product of (D2*G2), if colums D2, E2, G2 are blank or NA then return the value of zero. Can someone please help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum If - I think
You don't seem to be using the values from column F and H in your
description. Try this formula in I2: =IF(OR(D2="",D2="NA",G2="",G2="NA"),0,IF(OR(E2="", E2="NA"),D2*G2,IF (ISNUMBER(E2),E2,"not defined"))) You can see that you have not been very precise in your description - what happens if you have other text values in those cells? Hope this helps. Pete On May 5, 5:04*pm, CandiC wrote: I have a worksheet with data in Columns D, E, F, G, and H that I will use to calculate column I. If E2 contains a *numeric value, then transfer that value to I2, but if E2 is Blank or NA then use the product of (D2*G2), if colums D2, E2, G2 are blank or *NA then return the value of zero. Can someone please help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum If - I think
try this
=IF(ISERROR(OR(E2="",D2="",G2="")),0,IF(E20,E2,D2 *G2)) Does this do what you want? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "CandiC" wrote: I have a worksheet with data in Columns D, E, F, G, and H that I will use to calculate column I. If E2 contains a numeric value, then transfer that value to I2, but if E2 is Blank or NA then use the product of (D2*G2), if colums D2, E2, G2 are blank or NA then return the value of zero. Can someone please help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum If - I think
Ok. I C. There will only be numerical values in any of the cells with the
exception of a NA# value, because the lookup didn't produce any results in that colmn. I will try this and C if it works. thank you, Candi "Pete_UK" wrote: You don't seem to be using the values from column F and H in your description. Try this formula in I2: =IF(OR(D2="",D2="NA",G2="",G2="NA"),0,IF(OR(E2="", E2="NA"),D2*G2,IF (ISNUMBER(E2),E2,"not defined"))) You can see that you have not been very precise in your description - what happens if you have other text values in those cells? Hope this helps. Pete On May 5, 5:04 pm, CandiC wrote: I have a worksheet with data in Columns D, E, F, G, and H that I will use to calculate column I. If E2 contains a numeric value, then transfer that value to I2, but if E2 is Blank or NA then use the product of (D2*G2), if colums D2, E2, G2 are blank or NA then return the value of zero. Can someone please help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum If - I think
¦.D2...............E2¦¦...............G2¦ ¦¦¦¦.............¦..H2................... ¦¦I2¦¦.
¦Item QTY...Ext actual Cost... Unit Cost¦¦Imported Unit Cost¦¦¦¦.Actual Cost .....2¦¦¦..........1.00¦........¦..50¦ ..................89¦¦¦¦¦¦¦¦...1.0 0(=E2) ...10¦¦¦¦¦.<blank or 0¦..¦.25¦¦¦¦¦¦¦¦50¦¦¦. ¦¦¦¦..250(D2*G2) ¦5............¦<blank or 0¦¦<blankor 0¦¦¦¦10¦¦¦¦¦¦¦¦¦50(D2*H2 ) ¦20¦¦¦¦..<blank or 0¦¦¦<blankor 0¦¦<blankor 0¦¦¦¦¦¦¦.(0) Here is a summary of what I am looking at. D2= The item QTY, E2= the Actual extended cost of the item, G2 =Unit Costs, and H2= imported unit costs. In the event that I don't have an actual cost in columns E2 or G2 then I need to multiply H2 by the item qty in D2 to give me an extended cost. However, I would prefer to have the quantity specified in column E2 if there is one. Thank you for your help. "Pete_UK" wrote: You don't seem to be using the values from column F and H in your description. Try this formula in I2: =IF(OR(D2="",D2="NA",G2="",G2="NA"),0,IF(OR(E2="", E2="NA"),D2*G2,IF (ISNUMBER(E2),E2,"not defined"))) You can see that you have not been very precise in your description - what happens if you have other text values in those cells? Hope this helps. Pete On May 5, 5:04 pm, CandiC wrote: I have a worksheet with data in Columns D, E, F, G, and H that I will use to calculate column I. If E2 contains a numeric value, then transfer that value to I2, but if E2 is Blank or NA then use the product of (D2*G2), if colums D2, E2, G2 are blank or NA then return the value of zero. Can someone please help? |
#6
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|