Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formulae problems:HELP
hello there, i have jst started a new I.T job and need some help with excel,heres my problem, if cell A = 5014 then l - m. under a lot of pressure here can somone please help. -- stevenmorrison ------------------------------------------------------------------------ stevenmorrison's Profile: http://www.excelforum.com/member.php...o&userid=26188 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#2
|
|||
|
|||
We don't quite have enough information for a complete answer, but here's something to work with: Assuming your values begin in Cell A1, put this in another column on row 1 and copy down (eg. in Cell B1): =if(A1=5014,L1-M1,0) You didn't say what to do if Col A doesn't =5014, so that formula returns a zero. Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#3
|
|||
|
|||
thanks fro ure reply, i have 2 different values in collum A,5014 & 5015,stretch over about 40 rows. now i want the values which in l & m which are in the same rows as the 5014's to be included in the calculation, i just cant get the formulae to work. cheers -- stevenmorrison ------------------------------------------------------------------------ stevenmorrison's Profile: http://www.excelforum.com/member.php...o&userid=26188 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#4
|
|||
|
|||
Are you looking to summarize all L's minus M's for rows where Column A equals 5014? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#5
|
|||
|
|||
yes ron, im looking for the result of l -m to be the resut of my formulae where l + m are in the same row as any Cell A:5014's which may be on the spreadsheet. cheers again. -- stevenmorrison ------------------------------------------------------------------------ stevenmorrison's Profile: http://www.excelforum.com/member.php...o&userid=26188 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#6
|
|||
|
|||
OK...see if I'm on the right track here. Put one of these formulas in a cell anywhere but in columns A, L, or M: =SUMIF($A$1:$A$40,5014,$L$1:$L$40)-SUMIF($A$1:$A$40,5014,$M$1:$M$40) =SUMPRODUCT(--($A$1:$A$40=5014)*(($L$1:$L$40)-($M$1:$M$40))) Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#7
|
|||
|
|||
thanks ron ure the man, the top one works, havent tried the 1 below it. i cant thank u enough. any chance of talking me through that formulae =SUMIF($A$1:$A$40,5014,$L$1:$L$40)-SUMIF($A$1:$A$40,5014,$M$1:$M$40) -- stevenmorrison ------------------------------------------------------------------------ stevenmorrison's Profile: http://www.excelforum.com/member.php...o&userid=26188 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#8
|
|||
|
|||
Sure.... =SUMIF($A$1:$A$40,5014,$L$1:$L$40)-SUMIF($A$1:$A$40,5014,$M$1:$M$40) Try thinking of the SUMIF like this: =SUMIF( Find items in this range, That match this value, When you find them...add the corresponding items from this range) So, in your example, the first SUMIF: =SUMIF($A$1:$A$40,5014,$L$1:$L$40) Works this way: =SUMIF( Find Items in this range: $A$1:$A$40, That match: 5014, When you find them...add the corrsponding items from this range: $L$1:$L$40) The first SUMIF adds the total matched values from Column L. The 2nd SUMIF subtracts the total matched values from Column M. Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
#9
|
|||
|
|||
thanks ron thats great, ill b sure to keep an eye out for u if i have problems in the future. cheers steven -- stevenmorrison ------------------------------------------------------------------------ stevenmorrison's Profile: http://www.excelforum.com/member.php...o&userid=26188 View this thread: http://www.excelforum.com/showthread...hreadid=394944 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
Dynamic formulae - similar to lotus 123 for excel | Excel Discussion (Misc queries) | |||
XL2002 Clipboard doesn't paste formulae | Excel Discussion (Misc queries) | |||
Excel and CubeCell Formulae | Excel Discussion (Misc queries) | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions |