£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

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

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

Options

## All Comments

(31) Jump to unreadPost a comment=if(C6

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 ;-)

I find using Conditional formatting helps to highlight improvements etc

+1

: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.

lol

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

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))))))

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]

:?

:?

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

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")),"")

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'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!

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

lol

=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

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

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")

tadaaaa... lol

now just to practise till it seems ridiculously easy.

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)

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

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

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)

Where A1 = 5108

Change range as required for much larger spreadsheet.

Try it and see :)

## Post a Comment