tag:blogger.com,1999:blog-1869924468172210809.post5001132280450552252..comments2022-05-28T16:32:36.616+01:00Comments on Not Just Numbers: EXCEL TIP: The IF Statement made simpleGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger46125tag:blogger.com,1999:blog-1869924468172210809.post-22995628420230373012021-11-22T11:48:00.726+00:002021-11-22T11:48:00.726+00:00Thank you Glen for your helpThank you Glen for your helpKChttps://www.blogger.com/profile/01969707050254531562noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-58832557816776543312021-11-21T22:57:02.461+00:002021-11-21T22:57:02.461+00:00If this is what you want:
If K36 is 0 or positive ...If this is what you want:<br />If K36 is 0 or positive number then return D36,<br />If K36 is a negative number then return L36<br /><br />Then you need:<br /><br />=IF(K36>=0,D36,L36)<br /><br /><br /><br />Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-87721133771414601852021-11-21T22:13:30.944+00:002021-11-21T22:13:30.944+00:00I'm trying to do a capital gain tax calculatio...I'm trying to do a capital gain tax calculation based on the days in column K26. Strangely its working in some of the cells and not in the rest.<br />If I change the formula to =IF(K36<0,D36,L36) then it works. Not sure what I'm missing, spent a lot of time today in trying different things and reading lot of blogs but no luck.<br /><br />Appreciate your helpKChttps://www.blogger.com/profile/01969707050254531562noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-24140277806848872021-11-21T22:02:39.876+00:002021-11-21T22:02:39.876+00:00Hi Glen thanks for getting back to me.
If K36 is ...Hi Glen thanks for getting back to me.<br /><br />If K36 is 0 or positive number then return D36,<br />If K36 is a negative number then return L36<br /><br />Thank you very much for your help<br />kcKChttps://www.blogger.com/profile/01969707050254531562noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-48971458905927973172021-11-21T21:13:52.119+00:002021-11-21T21:13:52.119+00:00=IF(K36>0,D36,L36) should return D36 if K36 is ...=IF(K36>0,D36,L36) should return D36 if K36 is positive or L36 if K36 is zero or negative. Is that not what you want?Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-68399604149764594562021-11-21T18:38:07.579+00:002021-11-21T18:38:07.579+00:00Hello, Im trying to write an If statement for the ...Hello, Im trying to write an If statement for the following<br />Cell K36 has a number (could be negative or positive)<br />In Column M36 my if statement is =IF(K36>0,D36,L36)<br />This is working when the number is positive however if the number is negative its not returning the right value.<br />Please can you help<br />Many thanksKChttps://www.blogger.com/profile/01969707050254531562noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-50372900251927858162021-01-25T07:20:15.679+00:002021-01-25T07:20:15.679+00:00The simplest answer would be to add another column...The simplest answer would be to add another column with another IF formula:<br /><br />=IF(B14<>"",F14,0)<br /><br />Then sum that column.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-66186342570216380842021-01-24T21:49:57.183+00:002021-01-24T21:49:57.183+00:00I have another one for you after you were such a h...I have another one for you after you were such a huge help. I have column B which receives an x if an order was placed online. I have column F which is the total number of items ordered. What I need is a cell that will add the number in F if there is an x in B but to ignore the number if F if there is no x. I want a count of online ordered items.Cassiehttps://www.blogger.com/profile/06627726673245566405noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21347077860774417992018-08-29T19:46:59.431+01:002018-08-29T19:46:59.431+01:00I am using the IF function as such: =+IF(R3="...I am using the IF function as such: =+IF(R3="0", Q3). However, I only want the positive values in column Q to be reflected.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!SuQuhttps://www.blogger.com/profile/14176295104052911950noreply@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) ??????Anonymoushttps://www.blogger.com/profile/08120846656251988676noreply@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-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 />ThanksnOOrylwhttps://www.blogger.com/profile/08797182300743610405noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-40449139743594295772016-11-07T07:14:48.536+00:002016-11-07T07:14:48.536+00:00I would recommend calculating the amount above reg...I would recommend calculating the amount above regional target in a separate cell (say N2), then if you want to use the IF statement it would be something like:<br /><br />IF(N2>=5000,M9,IF(N2>=1000,M8,IF(N2>=500,M7,0)))<br /><br />However, the following poat fives a neater approach to thua kind of problem:<br /><br />http://www.notjustnumbers.co.uk/2012/10/excel-tip-look-up-commission-or-tax.htmlGlen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-13358065809245479902016-11-07T02:10:09.969+00:002016-11-07T02:10:09.969+00:00What if it's "-1,900", how can I put...What if it's "-1,900", how can I put that in a IF function?<br /><br />THIS IS THE EXACT QUESTION I NEED ANSWERED:<br /><br />If the units sold is 5,000 or more above the regional target, the employee gets the value in M9. If the units sold is 1,000 or more above the regional target, the employee gets the value in M8. If the units sold is 500 or more above the target, the employee gets the value in M7, otherwise they get $0.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-18892019500905925852016-08-19T06:41:59.092+01:002016-08-19T06:41:59.092+01:00Check your brackets. Your IFNA doesn't close u...Check your brackets. Your IFNA doesn't close until your final bracket (hence this is just one IFNA function). The rest of your function is just the condition for your IFNA function and it will return B2 if that whole function returns NA.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-50808735460292093192016-08-19T05:10:42.236+01:002016-08-19T05:10:42.236+01:00Any advice for me. Seems like formula is only cat...Any advice for me. Seems like formula is only catching IFNA<br />=IFNA(IF(AND(B2<0,C2<0),B2-C2)&IF(AND(B2<0,C2>0),B2+C2)&IF(AND(B2>0,C2<0),B2+C2)&IF(AND(B2>0,C2>0),B2-C2),B2)Sceptre Tourshttps://www.blogger.com/profile/07903679130036611450noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-10388180605566591962016-08-10T12:20:31.110+01:002016-08-10T12:20:31.110+01:00You need to leave d2 alone and use it in the formu...You need to leave d2 alone and use it in the formula in another column, say column E. So in E2, you would use the formula:<br /><br />=IF(a2="sales return",-d2,d2)<br /><br />Then use column E as your values.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-5899031411141669902016-08-10T12:15:24.984+01:002016-08-10T12:15:24.984+01:00Here's one for you!
My order book shows sales...Here's one for you!<br /><br />My order book shows sales orders AND sales returns as positive numbers. <br /><br />How would I construct an IF statement that says "if(a2="sales return",make the number in d2 a negative, leave as is)?<br /><br />Or is there another way...?<br /><br />Thanks in advance :)JaineyWhttps://www.blogger.com/profile/07639362792114658617noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-79183067705413762832016-03-01T00:32:37.030+00:002016-03-01T00:32:37.030+00:00Thanks for the feedback Glen.
Sorry I haven't...Thanks for the feedback Glen.<br /><br />Sorry I haven't had the chance to read all of your posts earlier but I am surely will do. :)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-76585598773145085322016-02-29T13:08:17.616+00:002016-02-29T13:08:17.616+00:00@tikun
You can use Nested IF statements - see this...@tikun<br />You can use Nested IF statements - see this earlier post:<br /><br />http://www.notjustnumbers.co.uk/2015/06/excel-tip-nested-if-statements.html<br /><br />However, a better solution would be to use a lookup table, as per this post:<br /><br />http://www.notjustnumbers.co.uk/2012/10/excel-tip-look-up-commission-or-tax.htmlGlen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.com