Halppppp! purleaseee, I'm going insane.. lol .. Excel help pretty please with bobbles on. - HotUKDeals
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

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

£0.00 @
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 … Read More
8y, 2m agoPosted 8 years, 2 months ago
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
8y, 2m agoPosted 8 years, 2 months ago
Options

(31)
#1
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...

1 Like #2
something like

=if(C6
#3
I thought Excel was a clothing size :?
1 Like #4
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:
#5
starsparkle2311
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#6
=IF('X'<'L', "IMPROVED", "DECREASED")

I find using Conditional formatting helps to highlight improvements etc
banned#7
AberBargoed;4199026
You would need to name a range for that to work ;-)

+1
#8
starsparkle2311

: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.
#9
You all started speaking Russian... STOP IT!!!

lol
banned#10
starsparkle2311;4199165

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#11
AberBargoed;4199196
: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))))))
#12
ODB_69
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))))))

[email protected]

:?
banned#13
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
#14
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
#15
@ 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#16
AberBargoed;4199284
@ 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
#17
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#18
AberBargoed;4199410
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
#19
Прекратите говорить на русском языке!!!!!!!!!!!

lol
#20
DazzyB
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#21
ChipSticks;4199575
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
#22
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?
#23
Remember to start the formula in column two of your range Chipsticks :thumbsup:
#24
Just use conditional formatting :thumbsup:

#25
starsparkle2311
That's what I learnt
Try 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")

now just to practise till it seems ridiculously easy.
banned#26
AberBargoed;4199615
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
#27
ODB_69
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)

No array needed :thumbsup:

Apologies if I'm barking up the wrong tree here...(feel free to rep me for effort though)
banned#28
AberBargoed;4200151
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
#29
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#30

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)
#31
=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 :)