Groups

    Halppppp! purleaseee, I'm going insane.. lol .. Excel help pretty please with bobbles on.

    Ok.. Long story short, I'm teaching myself excel at college but stuck, and as I'm doing this off my own back, can't ask them for help.

    and I'm stuck... lol

    I have a spreadsheet with some income totals over 6 months. I need to create a cell that ascertains if turnover is improving... so I have a row with 6 cells of figures and I need to create a cell underneath that says yes or no if turnover is improving.

    Does that even make sense? lol

    arghhhh

    31 Comments

    Original Poster

    So basically I have a cell for janurary, with xxxxx amount in, then the rest of the months up to june with xxxx amount in... I have to create a cell that says yes if the june cell is higher than the jan cell and no if the june cell is lower...

    something like

    =if(C6

    I thought Excel was a clothing size :?

    You could also use conditional formatting to highlight trends - i.e. green shading for increasing profit and red for when it falls. Easier to quickly understand than a bunch of yes/no answers. Got a sample spreadsheet somewhere I could send :thumbsup:

    starsparkle2311;4198973

    I've found my workbook, I had a similar one to do.I had to create a … I've found my workbook, I had a similar one to do.I had to create a formula, showing whether there was a Profit or Loss.The columns were Month, Income, Outgoings, Profit or Loss.=If(Income>Outgoings, "Profit","Loss")Don't forget the quote marks they're important, I think:oops:



    You would need to name a range for that to work ;-)

    Banned

    =IF('X'
    I find using Conditional formatting helps to highlight improvements etc

    Banned

    AberBargoed;4199026

    You would need to name a range for that to work ;-)


    +1

    starsparkle2311;4199165

    Way over my head:whistling::p



    :lol:

    You must be half decent to name a range and use it in a formula :thumbsup:

    Conditional formatting definitely the way to go - you can always add text to the cell too if need be.

    Original Poster

    You all started speaking Russian... STOP IT!!!

    lol

    Banned

    starsparkle2311;4199165

    Way over my head:whistling::p


    name ranges are basically when you call column A - "BOB" or soemthing..an actual name rather than A:A basically. I tend not to bother as I have that many things linking to certain cells it can cause me problems...plus we have that many sheets and links it would kill us to have named them all

    Banned

    AberBargoed;4199196

    :lol:You must be half decent to name a range and use it in a formula … :lol:You must be half decent to name a range and use it in a formula :thumbsup:Conditional formatting definitely the way to go - you can always add text to the cell too if need be.



    its not that hard tbh when you know how to do it...pretty simple

    example of what to me is a basic basic basic formula that we do everyday

    =(VLOOKUP(C6,Master_Data!D:BK,46,0)+VLOOKUP(C6,Master_Data!D:BK,47,0)+VLOOKUP(C6,Master_Data!D:BK,48,0)+VLOOKUP(C6,Master_Data!D:BK,49,0)+VLOOKUP(C6,Master_Data!D:BK,50,0)+VLOOKUP(C6,Master_Data!D:BK,51,0))-VLOOKUP(C6-1,'TCR INPUT'!D:AM,22,0)

    slightly trickier possibly because of IF statements

    =IF(E25>'New Report'!$C$6,"",IF((SUM(VLOOKUP(E25,Master_Data!D:U,16,0)+VLOOKUP(E25,Master_Data!D:U,17,0)+VLOOKUP(E25,Master_Data!D:U,18,0)))=0,"",(VLOOKUP(E25,Master_Data!D:U,12,0)/(SUM(VLOOKUP(E25,Master_Data!D:U,16,0)+VLOOKUP(E25,Master_Data!D:U,17,0)+VLOOKUP(E25,Master_Data!D:U,18,0))))))

    Original Poster

    ODB_69;4199218

    its not that hard tbh when you know how to do it...pretty simpleexample … its not that hard tbh when you know how to do it...pretty simpleexample of what to me is a basic basic basic formula that we do everyday=(VLOOKUP(C6,Master_Data!D:BK,46,0)+VLOOKUP(C6,Master_Data!D:BK,47,0)+VLOOKUP(C6,Master_Data!D:BK,48,0)+VLOOKUP(C6,Master_Data!D:BK,49,0)+VLOOKUP(C6,Master_Data!D:BK,50,0)+VLOOKUP(C6,Master_Data!D:BK,51,0))-VLOOKUP(C6-1,'TCR INPUT'!D:AM,22,0)slightly trickier possibly because of IF statements=IF(E25>'New Report'!$C$6,"",IF((SUM(VLOOKUP(E25,Master_Data!D:U,16,0)+VLOOKUP(E25,Master_Data!D:U,17,0)+VLOOKUP(E25,Master_Data!D:U,18,0)))=0,"",(VLOOKUP(E25,Master_Data!D:U,12,0)/(SUM(VLOOKUP(E25,Master_Data!D:U,16,0)+VLOOKUP(E25,Master_Data!D:U,17,0)+VLOOKUP(E25,Master_Data!D:U,18,0))))))



    [email protected]

    :?

    Banned

    ChipSticks;4199230

    [email protected]:?


    might look complicated but to me its not. When you know what its doing its quite simple...those for instance are just looking at particular columns and crossing them to the date its been told to look at and adding the numbers up in those columns...minusing certain other ranges off the total or divinding by a different set of numbers. So hard as it looks its basically a simple sum. So if all the numbers in those cells were 5 say its then

    (5+5+5+5+5)-5 for the first one...if u see what I'm getting at

    Original Poster

    I'm even more confused now. I'm basically being allowed to work by myself at college as I help out with the noobs. I'm trying to get my head round what I'm doing whilst teaching people how to format column headings and use autosum... my head hurts! lol

    @ ODB -

    Nothing too tricky about IF statements - IF you know what you are doing!

    Use them often, have the following in a Price Basket tool I designed -

    =IF($L9="in",IF(CONCATENATE($M9,$N9,$O9,$P9)"",SUM(IF(ISNUMBER(SEARCH("+in+",$B$35:$B$326)*SEARCH("+"&$M9,$B$35:$B$326)*SEARCH("+"&$N9,$B$35:$B$326)*SEARCH("+"&$O9,$B$35:$B$326)*SEARCH("+"&$P9,$B$35:$B$326)),$L$35:$L$326)),"no criteria"),"out")

    &

    =IF(AND(AW$2="in",AT37"",ISNUMBER(AU37),AU37>0),IF(Analysis_currency="EUR",AU37*VLOOKUP(AT37,'Exchange rates'!$C$5:$E$55,2,FALSE),IF(Analysis_currency="LOCAL",AU37*VLOOKUP(AT37,'Exchange rates'!$C$5:$E$55,3,FALSE),"no")),"")

    Banned

    AberBargoed;4199284

    @ ODB -Nothing too tricky about IF statements - IF you know what you are … @ ODB -Nothing too tricky about IF statements - IF you know what you are doing!Use them often, have the following in a Price Basket tool I designed - =IF($L9="in",IF(CONCATENATE($M9,$N9,$O9,$P9)"",SUM(IF(ISNUMBER(SEARCH("+in+",$B$35:$B$326)*SEARCH("+"&$M9,$B$35:$B$326)*SEARCH("+"&$N9,$B$35:$B$326)*SEARCH("+"&$O9,$B$35:$B$326)*SEARCH("+"&$P9,$B$35:$B$326)),$L$35:$L$326)),"no criteria"),"out")



    exactly...I dont find them tricky at all but have been doing it every day for about 3+ years now but I know others have problems with them, its just a case of understanding what you are looking to do and then understanding what your asking of it

    I've uploaded a ]conditional formatting file :thumbsup:

    I'm a bit of an Excel anorak - use it daily at work and am always finding new ways to save time etc. Usually work within VBA now but that often causes headaches!

    Banned

    AberBargoed;4199410

    I'm a bit of an Excel anorak - use it daily at work and am always finding … I'm a bit of an Excel anorak - use it daily at work and am always finding new ways to save time etc. Usually work within VBA now but that often causes headaches!


    in that case any idea on this one, have tried numerous Excel forums and the only answer I get is to create an array

    if I wanted to do an =SUMIF on 3 columns that are right next to each other is there something else that I can use to add all columns in that range?

    so say =SUMIF('Marie INPUT'!C:C,H58,'Marie INPUT'!ED:ED)+SUMIF('Marie INPUT'!C:C,H58,'Marie INPUT'!EE:EE)+SUMIF('Marie INPUT'!C:C,H58,'Marie INPUT'!EF:EF)

    I figure there must be something that would allow me to use 1 formula rather than multiple added together if you follow? where it would add ED:EF

    Original Poster

    Прекратите говорить на русском языке!!!!!!!!!!!

    lol

    Original Poster

    DazzyB;4198905
    something like

    =if(C6


    ok I tried that and it didn't work...

    Looking through my text book it's telling me to go to the paste function... I do that and I go to the "if" part.. then I'm getting 3 boxes, logical test, value if true, value if false... am I in the right part? I have no idea what to put in those boxes! lol

    Banned

    ChipSticks;4199575

    ok I tried that and it didn't work...Looking through my text book it's … ok I tried that and it didn't work...Looking through my text book it's telling me to go to the paste function... I do that and I go to the "if" part.. then I'm getting 3 boxes, logical test, value if true, value if false... am I in the right part? I have no idea what to put in those boxes! lol


    are you trying to use that example in the boxes or the actual cell? should work tbh..I never use the boxes, just do it manually, cant even remember how there set up lol

    I don't suppose you have a 'clean' file you could send do you ODB? Just about understand what you are trying to do - if I get a chance I'll try and come up with something today (array sounds familiar though), have you tried Mr Excel et al?

    Remember to start the formula in column two of your range Chipsticks :thumbsup:

    Just use conditional formatting :thumbsup:

    http://www.freeimagehosting.net/uploads/43940640f6.gif

    Original Poster

    starsparkle2311;4199660

    That's what I learntTry here … That's what I learntTry here http://www.timeatlas.com/mos/5_Minute_Tips/General/Excel_Basics_and_the_IF_Function/value if true=yes value if false=no



    Yaaaaaaaaaay... lol

    Ok it finally clicked.

    It was how you guys said, but for some reason it didn't work when I just typed it in... when I filled in the boxes it worked.

    First box b17>g17
    next box yes
    last box no

    =IF(B17>G17,"no","yes")


    tadaaaa... lol

    now just to practise till it seems ridiculously easy.

    Banned

    AberBargoed;4199615

    I don't suppose you have a 'clean' file you could send do you ODB? Just … I don't suppose you have a 'clean' file you could send do you ODB? Just about understand what you are trying to do - if I get a chance I'll try and come up with something today (array sounds familiar though), have you tried Mr Excel et al?



    could create one I suppose...it is quite hard to explain over a forum but basics are a SUMIF stule formula but rather than have SUMIF AA + SUMIF AB + SUMIF AC it would be something like AA:AC...so however many columns in sequence add up in 1 formula rather than each column being added together

    ODB_69;4199933

    could create one I suppose...it is quite hard to explain over a forum but … could create one I suppose...it is quite hard to explain over a forum but basics are a SUMIF stule formula but rather than have SUMIF AA + SUMIF AB + SUMIF AC it would be something like AA:AC...so however many columns in sequence add up in 1 formula rather than each column being added together



    Assuming I am understanding your problem correctly....

    What about replacing SUMIF with SUMPRODUCT?

    Works for me on a table with three columns of data I want to sum up based on a specific criteria (i.e. year)

    http://www.freeimagehosting.net/uploads/ababe952bc.gif

    No array needed :thumbsup:

    Apologies if I'm barking up the wrong tree here...(feel free to rep me for effort though)

    Banned

    AberBargoed;4200151

    Assuming I am understanding your problem correctly....What about … Assuming I am understanding your problem correctly....What about replacing SUMIF with SUMPRODUCT?Works for me on a table with three columns of data I want to sum up based on a specific criteria (i.e. year)No array needed :thumbsup:Apologies if I'm barking up the wrong tree here...(feel free to rep me for effort though)



    no thats close, but what i would need is something that allows to search for a particular range.

    so say the dates are down Column A and the info is along the column list, so like you have said above BUT searching by a particular date. So adding the figures you have above but ONLY for 2002 say

    You might need to run that by me again :thinking::oops:

    If you change 2009 (cell B21) to 2002 the formula will total all 'sales' from that year and returns 1917.

    Banned

    http://i279.photobucket.com/albums/kk141/ODB_69/untitled-1.jpg


    So here I could the different columns together by using a Vlookup on the date OR a SUMIF on the week. This would require multiple formulae added together. But what i was looking for is a way of saying on this particular week/date add ALL the columns together using just 1 formula and no need to add a load of formulas together

    so on the sheet it would link too, saying the criteria was Week 51, instead of going =sumif(A:A, 5108, D:D)+sumif(A:A, Week 51, E:E)+sumif(A:A, 5108, F:F) it would be something like =SUMIFX(A:A, 5108, D:F)

    =SUMPRODUCT(($A$4:$A$9=A1)*$D$4:$F$9)

    Where A1 = 5108

    Change range as required for much larger spreadsheet.

    Try it and see
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. WIN AN UNFORGETTABLE TRIP TO SAN FRANCISCO11
      2. LG G6 International [email protected] droidauthority22
      3. Win a luxury eight-night break for two in Malaysia with Ushvani Spa and Tra…77
      4. mail on sunday comp - win a £3,000 thomas cook gift e-card33

      See more discussions