Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
Hi All, I have a very basic knowledge of excel so please forgive me if this is actually ridiculously easy to do. What i'm trying to do is this: I have a basic addition formula in A1. What i am trying to do is have another cell read A1 and if there is any amounts of 2000 or more in the addition sum i want it to say "amount of 2000" or what ever the amount is. So if the sum was say: 100+200+300+400. It wouldn't do anything but if the sum was 100+200+2564. It would say "Amount of 2564". All i have been able to do so far is have this message come up if the result of the actual sum is 2000 or over but i can't get it to just read the individual amounts in the sum.The formula i have been using is: =IF((A1=2000),"Amount of " &A1,"") I hope this makes sense, if anyone can help i would greatly appreciate it. Thanks. -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
What formula is in A1? Is it a Sum of a range? -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
Just a normal addition sum. =100+200+300 -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
To be honest, I don't know how you could extract it without using a lengthy Visual Basic Module. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
I'm not sure I completely understand what you're trying to do.
So, you want to look at the individual amounts in cell A1, not the actual value of A1, to see if any are 2000 or greater? So, 600+600+600+600 would not be displayed, since there are no individual values greater than 2000, even though the total value is 2400? Is this correct? If so, what is the reason for keeping all values in the same cell rather than seperate cells? This makes the formula much more difficult, especially since you don't seem to have the same number of values being added together. What I'd recommend is placing your values in seperate cells, lets say A1 through A5. Then your formula could be =IF(MAX(A1:A5)=2000,"Amount of "&SUM(A1:A5),"") If this won't work, please post back with more details. HTH, Elkar "vertigo" wrote: Hi All, I have a very basic knowledge of excel so please forgive me if this is actually ridiculously easy to do. What i'm trying to do is this: I have a basic addition formula in A1. What i am trying to do is have another cell read A1 and if there is any amounts of 2000 or more in the addition sum i want it to say "amount of 2000" or what ever the amount is. So if the sum was say: 100+200+300+400. It wouldn't do anything but if the sum was 100+200+2564. It would say "Amount of 2564". All i have been able to do so far is have this message come up if the result of the actual sum is 2000 or over but i can't get it to just read the individual amounts in the sum.The formula i have been using is: =IF((A1=2000),"Amount of " &A1,"") I hope this makes sense, if anyone can help i would greatly appreciate it. Thanks. -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
Elkar Wrote: I'm not sure I completely understand what you're trying to do. So, you want to look at the individual amounts in cell A1, not the actual value of A1, to see if any are 2000 or greater? So, 600+600+600+600 would not be displayed, since there are no individual values greater than 2000, even though the total value is 2400? Is this correct? If so, what is the reason for keeping all values in the same cell rather than seperate cells? This makes the formula much more difficult, especially since you don't seem to have the same number of values being added together. What I'd recommend is placing your values in seperate cells, lets say A1 through A5. Then your formula could b =IF(MAX(A1:A5)=2000,"Amount of "&SUM(A1:A5),"") If this won't work, please post back with more details. HTH, Elkar Sorry i should have explained myself better, yes Elkar i want it to just show up individual amounts of 2000 and more, but not when i have a sum of say 600+600+600+600 = 2400. The reason they are all in the one cell is because the numbers which will go into A1 varies each time.Sometimes it can be just two amounts and sometimes it can be 10+ so it would be very messy having loads of cells. -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
=IF(OR(a1=2000,a12000),"amount of"&a1,"")
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
Wrote: =IF(OR(a1=2000,a12000),"amount of"&a1,"") Wont that formula just give the value of A1? Not the value of any amount over 2000 in the sum? -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
Unfortunately, there is no easy way to do this then.
What if you had a second worksheet to store the numbers in? Say, column A of Sheet2? This way you could enter as many numbers as you like, but your main sheet would remain clean. =IF(MAX('Sheet2'!A:A)=2000,"Amount of "&SUM('Sheet2'!A:A),"") Your other option would be to use VB Code, which I'm not real efficient at writing. Perhaps someone in the Excel Programming forum could assist there. HTH, Elkar "vertigo" wrote: Elkar Wrote: I'm not sure I completely understand what you're trying to do. So, you want to look at the individual amounts in cell A1, not the actual value of A1, to see if any are 2000 or greater? So, 600+600+600+600 would not be displayed, since there are no individual values greater than 2000, even though the total value is 2400? Is this correct? If so, what is the reason for keeping all values in the same cell rather than seperate cells? This makes the formula much more difficult, especially since you don't seem to have the same number of values being added together. What I'd recommend is placing your values in seperate cells, lets say A1 through A5. Then your formula could b =IF(MAX(A1:A5)=2000,"Amount of "&SUM(A1:A5),"") If this won't work, please post back with more details. HTH, Elkar Sorry i should have explained myself better, yes Elkar i want it to just show up individual amounts of 2000 and more, but not when i have a sum of say 600+600+600+600 = 2400. The reason they are all in the one cell is because the numbers which will go into A1 varies each time.Sometimes it can be just two amounts and sometimes it can be 10+ so it would be very messy having loads of cells. -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
Ok Elkar will try that, thanks for your help. Greatly appreciated. -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone help please?
I agree with Elkar, that you should use another sheet as input range for A1.
You can then check the individual cells in this range, to get your result. something like =IF(MAX('Sheet2'!A:A)=2000,"Amount of "&MAX('Sheet2'!A:A),"") "vertigo" wrote: Wrote: =IF(OR(a1=2000,a12000),"amount of"&a1,"") Wont that formula just give the value of A1? Not the value of any amount over 2000 in the sum? -- vertigo ------------------------------------------------------------------------ vertigo's Profile: http://www.excelforum.com/member.php...o&userid=35504 View this thread: http://www.excelforum.com/showthread...hreadid=552743 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|