tag:blogger.com,1999:blog-1869924468172210809.comments2018-09-12T18:48:29.386+01:00Not Just NumbersGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger1149125tag:blogger.com,1999:blog-1869924468172210809.post-54495972826344192382018-09-12T18:33:56.128+01:002018-09-12T18:33:56.128+01:00Hey I never knew this! Rather like how you can use...Hey I never knew this! Rather like how you can use shift + F9 when debugging VBA macros or .Net programming in Visual Studio to peek at the value of an expression. Thanks. I try and avoid complicated formulas because they are so damn difficult to read. But sometimes it is a trade off between a lots of confusing columns and sheets or complicated formulas. Now at least I have way (and my customers too) of evaluating quickly what each bit of a formula is doing. So thanks again for the tip off.Jim Truscotthttps://www.blogger.com/profile/04795494311746888733noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-74528959423061072722018-08-30T23:58:20.750+01:002018-08-30T23:58:20.750+01:00Great content
Great content<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-61026985992504463612018-02-26T17:51:18.818+00:002018-02-26T17:51:18.818+00:00Try this. You will need a helper row though.
We...Try this. You will need a helper row though.<br />We'll assume your data covers 52 columns, i.e. column B to column BA.<br />In cell B3 insert the formula:<br />=A3+B2<br />and copy along to column BA (make sure there is nothing in cell A3)<br />We can then use INDEX and MATCH to solve you problem:<br />=INDEX($B$1:$BA$1,MATCH($A$2,$B$3:$BA$3,1)+1)<br /><br />The MATCH function identifies the last column in our cumulative range that is still below cell A2. We add one to this to be the column which exceeds it. The INDEX then returns the corresponding value from row 1.<br />Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-89496048326419940922018-02-25T23:28:49.783+00:002018-02-25T23:28:49.783+00:00I have one for you. I am looking for a formula tha...I have one for you. I am looking for a formula that can stop when it reaches a negative number in the row and return the value in row 1 of the column it turned negative in. Ie, if I have X carrots and I want to figure out when I will run out of carrots, knowing the weekly demand for the carrots... (terrible example but hopefully you understand). I'm using if(sum(b2:c2)>a2,c1) then I'm filtering out the date returned from the equation and changing to if(sum(b2:d2)>a2,d1) and so on with the filtering. This gets tiring after a while especially when you are looking out weekly over the course of 12 months:( In this example row 1 is a date and the columns start with the carrots I have and continue with the demand for said carrots... Any help appreciated!Unknownhttps://www.blogger.com/profile/14176295104052911950noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-19231908892721790152018-02-23T06:45:43.439+00:002018-02-23T06:45:43.439+00:00Everything is very open with a very clear explanat...Everything is very open with a very clear explanation of the challenges.<br />It was really informative. Your site is extremely helpful.<br />Thank you for sharing!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21886746166819446982018-01-19T19:01:57.771+00:002018-01-19T19:01:57.771+00:00Very helpful indeed... I was amazed by what i just...Very helpful indeed... I was amazed by what i just read about your explanation. I have encountered this so many times and long time ago. I have just realised its function just now. Thank you so much. it is a great help. I hope you will not stop spreading what you have learned.Unknownhttps://www.blogger.com/profile/02367021361323212996noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-64463633229879955792017-12-20T13:58:01.391+00:002017-12-20T13:58:01.391+00:00Hi Glen,
You're such an angel.. thanks a bunc...Hi Glen,<br /><br />You're such an angel.. thanks a bunch... Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-6850671163814953682017-12-20T13:43:19.613+00:002017-12-20T13:43:19.613+00:00Try:
=IF(AND(D5=0,C5=0),0,IF(AND(D5>0,C5=0),1,(...Try:<br />=IF(AND(D5=0,C5=0),0,IF(AND(D5>0,C5=0),1,(D5-C5)/C5))Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-45231061198908151802017-12-20T13:24:03.161+00:002017-12-20T13:24:03.161+00:00(D5-C5)/C5)
Hi need help, want to see result 100% ...(D5-C5)/C5)<br />Hi need help, want to see result 100% if D5>0 while C5 is 0 and 0% if c5 and D5 are 0 and of course the right % if D5 and C5 have numbers. Thanks!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-56333333012583846982017-12-13T12:50:51.930+00:002017-12-13T12:50:51.930+00:00Such a relief to come across a poor man's Quic...Such a relief to come across a poor man's Quickbooks. Thank you very much. HAVE A MERRRRRRRRRRY CHRISTMAS.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-56620162513332043372017-11-23T12:47:58.739+00:002017-11-23T12:47:58.739+00:00Thanks problem resovledThanks problem resovledV.V.P Narasimhamhttps://www.blogger.com/profile/00676902520745706107noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-577300710697468552017-11-21T18:22:03.285+00:002017-11-21T18:22:03.285+00:00No. Although some laptops often have the function ...No. Although some laptops often have the function keys on other keys and another key to select the function.<br /><br />Also, make sure that you are editing the cell reference when you press it.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-77293025665786424312017-11-21T15:57:27.936+00:002017-11-21T15:57:27.936+00:00F4 doesn't work for me. WIN 7, Excel 2016
F2 w...F4 doesn't work for me. WIN 7, Excel 2016<br />F2 works for getting into the cell.<br />F4 does not toggle dollar signs. Is there some other key you need to press with F4?Unknownhttps://www.blogger.com/profile/03963450825206544201noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-71289578124642567862017-11-19T14:59:59.962+00:002017-11-19T14:59:59.962+00:00Thx bro!Thx bro!Zahid Efehttps://www.blogger.com/profile/11965597837619898183noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-175061502625816052017-11-18T17:30:21.973+00:002017-11-18T17:30:21.973+00:00Great, thanks, helped me a lotGreat, thanks, helped me a lotUnknownhttps://www.blogger.com/profile/12773033146045037992noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-78740838849524771882017-11-12T07:53:50.118+00:002017-11-12T07:53:50.118+00:00I might revisit this in a future post, but feel fr...I might revisit this in a future post, but feel free to ask any specific questions in the comments.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-54630309622344502852017-11-12T02:34:14.849+00:002017-11-12T02:34:14.849+00:00Can you tell us more about this? I'd want to f...Can you tell us more about this? I'd want to find out more details.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-9452139508273401382017-11-09T12:21:21.096+00:002017-11-09T12:21:21.096+00:00thank uthank ujohnnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-63827268184919623942017-10-27T13:14:59.648+01:002017-10-27T13:14:59.648+01:00Really trustworthy blog. Please keep updating with...Really trustworthy blog. Please keep updating with great posts like this one. I have booked marked your site and am about to email it to a few friends of mine that I know would enjoy reading. <br /><br /><a href="http://www.learnvern.com/course/basics-of-ms-excel" rel="nofollow">MS Excel</a>Dhara Patelhttp://www.learnvern.com/course/basics-of-ms-excelnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-28091877646154793672017-10-19T17:31:01.047+01:002017-10-19T17:31:01.047+01:00I love the way you write and share your niche! Ver...I love the way you write and share your niche! Very interesting and different! Keep it coming! <a href="http://www.thesalesguys.co.uk/" rel="nofollow">Business consultancy</a><br />julia annhttps://www.blogger.com/profile/17454961560287048593noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-44881012040139758622017-10-16T06:29:31.982+01:002017-10-16T06:29:31.982+01:00See aboveSee aboveGlen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-33805959740108954082017-10-16T06:29:22.894+01:002017-10-16T06:29:22.894+01:00Try:
=IF(E2-B2>=0,"Increased by ",&qu...Try:<br />=IF(E2-B2>=0,"Increased by ","Decreased by ")&TEXT(ABS(E2-B2),"$#,##0.00")<br /><br />The & character allows you to add text strings together.<br />The TEXT function converts a number to a string in a specified number format.<br />The ABS function returns the absolute value of a number, ignoring the +/-.<br /><br />Take a look at my post on using data in sentences for more details:<br />http://www.notjustnumbers.co.uk/2012/11/excel-tip-using-data-in-sentences.htmlGlen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-43870859230992259642017-10-16T02:06:43.356+01:002017-10-16T02:06:43.356+01:00How do I create a statement that will display the ...How do I create a statement that will display the sentence "increased by $_._" if there was an increase in a price (ex: E2-F2, E3-F3), or "Decreased by $_._" if there was a decrease in the price ????? Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-80599188179649199432017-10-16T02:04:07.352+01:002017-10-16T02:04:07.352+01:00How do I do an IF statement with words and numbers...How do I do an IF statement with words and numbers? My question is asking me to create a statement where I need to look at the difference between two columns (ex: E2-B2,E3-B3) and make a statement that will display the sentence "Increase by $_._" if there was an increase (positive) or "Decreased by $_._" of there was a decrease (negative) ??????Shannon Pagehttps://www.blogger.com/profile/08120846656251988676noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-66708389814540233762017-10-13T11:54:47.623+01:002017-10-13T11:54:47.623+01:00That's correct, as you have told it to return ...That's correct, as you have told it to return 100% if D2=0 OR C2=0, so it will always return 100% if D2 is 0, irrespective of the value of C2.<br /><br />You would only get an error if C2 was 0, so I'm not sure it matters if D2 is 0.<br /><br />This will avoid errors:<br /><br />=IF(C2=0,1,(D2-C2)/C2)<br /><br />as it will return 100% if it was going to divide by 0.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.com