Posts by Zpichette

    Re: If function help


    One more thing, say i were to copy the values to another sheet where there are only the returned numbers but theres like 6000 cells with varying sized gaps between each value is there any easy way to delete all the blank cells inbetween those values?

    Re: If function help


    Works perfectly! thanks for explaining to me the difference between the Iserr or NA as well much apprechiated!

    Re: If function help


    i want the if separate from the vlookup in column C im not looking to change the NA in cloumn B rather have cell A returned in Cell C when B is NA

    Alright so i did a vlookup and have a long list of numbers in cloumn A and the results of the vlookup in column B, Some of the values in column B say "#N/A"
    I want to return the value in cloumn A if Column B says "#N/A" and if it doesnt say NA i want the cell to remain blank.


    I tried =if(B1="#N/A",A1) this still returns an #N/A and i dont know how to proceed.


    Further more id like to get rid of those blanks between the numbers if at all possible but i can do this manually if there is no easy way to do it.


    Thanks in advance!

    Re: More Sum If Help Please


    Quote from Fotis1991;681795

    =SUMPRODUCT(($A$2:$A$20="mex")*($B$2:$B$20>0)*($B$2:$B$20)) :question:


    Perfect!!


    Can you explain to me what this formula is doing? how does it know to only add the last column if you have so many arrays im not familiar witht he sum product function.

    Re: More Sum If Help Please


    For post #4 that combines everything i need my sums separate essentially i need four different formulas that will give me the sums described in my first post
    Mex and positive
    Mex and negative
    And so on

    SO in cloumn A i have country codes and Column B i have prices.
    Example,


    [TABLE="width: 128"]

    [tr]


    [TD="width: 64"]Mex[/TD]
    [TD="width: 64, align: right"]10[/TD]

    [/tr]


    [tr]


    [td]

    usa

    [/td]


    [TD="align: right"]10[/TD]

    [/tr]


    [tr]


    [td]

    mex

    [/td]


    [TD="align: right"]-5[/TD]

    [/tr]


    [tr]


    [td]

    usa

    [/td]


    [TD="align: right"]-5[/TD]

    [/tr]


    [tr]


    [td]

    mex

    [/td]


    [TD="align: right"]3[/TD]

    [/tr]


    [tr]


    [td]

    usa

    [/td]


    [TD="align: right"]3[/TD]

    [/tr]


    [tr]


    [td]

    mex

    [/td]


    [TD="align: right"]-4[/TD]

    [/tr]


    [tr]


    [td]

    usa

    [/td]


    [TD="align: right"]-4[/TD]

    [/tr]


    [/TABLE]



    I need four formulas that work, One that sums mexico > 0 , mexico < 0, Usa > 0 , and Usa < 0


    How would i format this sumif function or is that even the right formula to use?


    My attempt was =sumif(A1:B8,"mex",sumif(A1:B8,>0,B1:B8)) Which did absolutely nothing for me...


    Please and thank you!

    Re: If Function problem


    Ok so i got something to work and am using two formulas for the separate columns H/K and J/G but if there is someway to consoloidate these rfunction into one that works that would be awesome to know.


    =IF(K6="JPY",J6*$V$30,IF(K6="eur",J6*$V$28,IF(K6="mxn",J6*$V$29,IF(K6="CDN",J6*$V$27,"Correct"))))
    and
    =IF(H6="JPY",G6*$V$30,IF(H6="eur",G6*$V$28,IF(H6="mxn",G6*$V$29,IF(H6="CDN",G6*$V$27,"correct"))))

    Alright soi im trying to make an if function that will check two columns and change a price depending on what in that column and id like it to function for both cloumns.


    What i tried was =if(k6="EUR",j6*V28,j6,if(h6="EUR",g6*v28,g6)) v28 is the exchange rate and columns J and G are prices and H and K are currency labels. Im not familiar with &'s / ors but i think that might help because the one i made isnt working.


    Please help!

    Re: Vlookup needs revision


    Alright next step.... Final inqury about this sheet.


    So some of the currencies of the two different files are conflicting what kind of formula would i use (in column P) to fix this i.e. make it so they are all usd.


    The exchange rates i must use are
    [TABLE="width: 141"]

    [tr]


    [td]

    CDN=>USD

    [/td]


    [TD="align: right"]0.97[/TD]

    [/tr]


    [tr]


    [td]

    Euro=>USD

    [/td]


    [TD="align: right"]1.3194[/TD]

    [/tr]


    [tr]


    [td]

    MXN=>USD

    [/td]


    [TD="align: right"]0.0790[/TD]

    [/tr]


    [tr]


    [td]

    JPY =>USD

    [/td]


    [TD="align: right"]0.01[/TD]

    [/tr]


    [/TABLE]

    Re: Vlookup needs revision


    Alright so ive made some modifications to the sheet to make everything more clear, Here is the link to the updated version of this file. I want to get rid of the NA's there is aprroximately 47% of the cells in column J returning NA which shouldnt be the case because if you manually check the part numbers and the prices match up so there should be no reason for this NA please tell me what the heck is going on here....


    http://db.tt/Q2Kxcpd9

    Re: Vlookup needs revision


    I dont think this works is excel 2003 or i am doing something wrong... can you make that column H work and send me the file becuase my tiny brain doesnt work very well.