 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006Can 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.  Signaturevertigo What formula is in A1? Is it a Sum of a range? SignatureBearacade Just a normal addition sum. =100+200+300 Signaturevertigo To be honest, I don't know how you could extract it without using lengthy Visual Basic Module -- Bearacad 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 > Hi All, > [quoted text clipped - 22 lines] > > Thanks. Elkar Wrote: > I'm not sure I completely understand what you're trying to do. > [quoted text clipped - 23 lines] > 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. Signaturevertigo 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 > Elkar Wrote: > > I'm not sure I completely understand what you're trying to do. [quoted text clipped - 33 lines] > and sometimes it can be 10+ so it would be very messy having loads of > cells. Ok Elkar will try that, thanks for your help. Greatly appreciated. Signaturevertigo =IF(OR(a1=2000,a1>2000),"amount of"&a1,"") ES.Solomon@gmail.com Wrote: > =IF(OR(a1=2000,a1>2000),"amount of"&a1,"") Wont that formula just give the value of A1? Not the value of any amount over 2000 in the sum? Signaturevertigo 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),"") > ES.Solomon@gmail.com Wrote: > > =IF(OR(a1=2000,a1>2000),"amount of"&a1,"") > > Wont that formula just give the value of A1? Not the value of any > amount over 2000 in the sum? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.