tag:blogger.com,1999:blog-1869924468172210809.comments2017-11-21T14:07:03.558+00:00Not Just NumbersGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger1136125tag: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.comtag:blogger.com,1999:blog-1869924468172210809.post-29971134875859627562017-10-13T05:29:59.942+01:002017-10-13T05:29:59.942+01:00=IF(OR(D2=0,C2=0),1,(D2-C2)/C2) However if D2=0 it...=IF(OR(D2=0,C2=0),1,(D2-C2)/C2) However if D2=0 it returns 100% even if C2 has value such as 1 or more. I am trying to calculate percentage between C2 and D2 and if D2 is lower than C2 then show a minus % and if D2 is higher than C2 then Positive %Photography Help & Tutorialshttps://www.blogger.com/profile/14823103130219809713noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-22087079733892881512017-10-04T16:20:15.744+01:002017-10-04T16:20:15.744+01:00I've wondered for years why there was no quick...I've wondered for years why there was no quick way to do this. But I guess there is! Thanks!Unknownhttps://www.blogger.com/profile/17214890407035387884noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-16548500466309684412017-09-28T10:33:51.720+01:002017-09-28T10:33:51.720+01:00This is another great tip, thank you so much!This is another great tip, thank you so much!Abigail Harperhttps://www.blogger.com/profile/02809181721289431018noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-9259986875664112342017-09-28T09:48:04.641+01:002017-09-28T09:48:04.641+01:00How about:
=IFERROR((F3/G3)*100,0)
?How about:<br /><br />=IFERROR((F3/G3)*100,0)<br /><br />?Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-89765851433608777172017-09-28T08:41:58.464+01:002017-09-28T08:41:58.464+01:00Can anyone help me with this, my formula is to cal...Can anyone help me with this, my formula is to calculate the percentage so goes (F3/G3)*100 but if F3 or G3 is zero I am getting the #DIV/0! error, how can I get it to just display a zero if F30 or G3 haze zero value or I leave them empty?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-47306719066490067092017-09-27T17:33:55.509+01:002017-09-27T17:33:55.509+01:00Thanks Jim. That's useful. I'd never tried...Thanks Jim. That's useful. I'd never tried it with charts!Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-78374612093015729792017-09-27T17:22:26.745+01:002017-09-27T17:22:26.745+01:00NB to clarify; you need to select EACH series and ...NB to clarify; you need to select EACH series and F9 separately to unlink the whole chart<br /><br />and if there are too many data points then it doesn't work<br /><br />jim againAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21958572449409508362017-09-27T17:17:30.436+01:002017-09-27T17:17:30.436+01:00yup, this is soooooo useful!
one of the most usefu...yup, this is soooooo useful!<br />one of the most useful-yet-little-known methods IMO<br /><br />Can also be used to replace externally-linked bits of a formula with their values (NB press Return this time)<br /> or to replace charted ranges with values (select a charted series, click the formula bar and press F9, Return) - you can then copy the chart to another workbook without it linking back<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-5309899114182910152017-09-20T02:05:16.154+01:002017-09-20T02:05:16.154+01:00Nice article SUMPRODUCT rules!
SUMIF + COUNTIF + ...Nice article SUMPRODUCT rules!<br /><br />SUMIF + COUNTIF + SUMIFS + COUNTIFS do not work on closed files - SUMPRODUCT does.<br /><br />SUMPRODUCT also handles leading zeroes in codes correctly - the others don't.<br /><br />The others can't use functions within their brackets SUMPRODUCT can and that is its real super power.<br /><br />eg this sums every second row<br />=SUMPRODUCT((MOD(ROW($A$2:$A$9),2)=1)*($A$2:$A$9))<br /><br />I use the * between the brackets as this is easier to type and explain since when you multiply by true it acts as one and false acts as zero. (It is slightly slower to use * calculation wise)<br /><br />Regards<br /><br />NealeNeale Blackwoodhttps://a4accounting.com.au/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-28178337156442592942017-09-11T18:58:22.363+01:002017-09-11T18:58:22.363+01:00If I understand correctly, your issue is not with ...If I understand correctly, your issue is not with the formula but with number format of the cell. Right-click on the cell containing the formula and select format cell, then on the Number Format tab choose Number and select the number of decimal places you want. This determines how the number is displayed. 1 and 1.0 are the same number, just different eays of displaying it, so you adding .0 to the formula didn't change anything.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-48188321865083911632017-09-11T17:41:24.743+01:002017-09-11T17:41:24.743+01:00I know I am about 3 years too late, and you will n...I know I am about 3 years too late, and you will not see this, but someone else may read it. First of all - Microsoft having a "monopoly" on the PC has absolutely nothing to do with this issue in the least. Whether or not Microsoft has a monopoly on PCs has no bearing at all on what office software you use. You could use Google Docs, or any number of other softwares, including Apple's. <br /><br />Furthermore, Microsoft ceasing support of Office 2013 is nothing like Ford or any other car company monitoring recall issues, having repair parts, or any other such nonsense. And, NO, we do not need those kinds of "rules" enforced today, because that is just stupid. Who on God's green earth thinks that their software is supposed to last 20 years? Think about what you're saying... That's quite ignorant. Also, having to program in backwards compatibility stifles progress in programming, and in their being able to offer newer and better developed tools and resources. -------- Oh, and by the way, the ignorant car comparison isn't a one to one comparison. It's not like a car company has to take part of their last car and build it into their newest one while making sure that it looks great and performs better than anyone elses. That's be like having a 2017 Fiesta with part of a 1997 Fiesta built in, and everything from the 1997 model still has to work correctly, while the 2017 has to be new and fresh. The whole thought of it is just stupid. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-91588227096696951602017-09-11T10:40:05.768+01:002017-09-11T10:40:05.768+01:00Hi,
I was hoping to get some help with my formula...Hi,<br /><br />I was hoping to get some help with my formula below:<br /><br />My cell E32 is interchangeable, could be 50, 45, 32, 0, e.t.c. I want the returned value to be in two (2) decimal place if condition is met in the formula. instead of just displaying 1, 2, 3, 4,5 in my formula it should give me exact value in 1 or 2 decimal places. i tried to add .0 to the formula but each time it keeps disappearing.<br /><br />=IF(AND(E32>0,E32<2.2),1,IF(AND(E32>2.2,E32<4.4),2,IF(AND(E32>4.4,E32<6.6),3,IF(AND(E32>6.6,E32<8.8),4,IF(AND(E32>8.8,E32<=11),5,"Out of Range")))))<br /><br />ThanksUnknownhttps://www.blogger.com/profile/08797182300743610405noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-87852183240239539752017-09-05T06:27:18.834+01:002017-09-05T06:27:18.834+01:00Can you please show the actual formula that is not...Can you please show the actual formula that is not working? Also, I am unsure of any differences for a Mac - maybe you need to include the full filepath, rather than just the sheet name. I don't have access to a Mac to test that.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.com