tag:blogger.com,1999:blog-1869924468172210809.post6189169221455801116..comments2017-04-25T09:49:05.413+01:00Comments on Not Just Numbers: EXCEL TIP: The dollar sign ($) in a formula - Fixing cell referencesGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger165125tag:blogger.com,1999:blog-1869924468172210809.post-78116414297090181622017-03-29T17:08:47.521+01:002017-03-29T17:08:47.521+01:00Thanks a lo!! That's great !Thanks a lo!! That's great !Chhabi Acharyahttp://www.blogger.com/profile/04852814382570438438noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-8647752904238338372017-03-29T15:25:12.310+01:002017-03-29T15:25:12.310+01:00If your sale price is in cell A1 then:
=50+IF($A$...If your sale price is in cell A1 then:<br /><br />=50+IF($A$1<=10000,0,$A$1*.00018)Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-27090553966113539322017-03-29T15:17:25.111+01:002017-03-29T15:17:25.111+01:00I am trying to calculate a formula that I want a f...I am trying to calculate a formula that I want a fix value and then the formula calculated by a percentage. Therefore if my sale price is < or = than $10,000.00 my fee is $50.00 but if my sale price is > or = $10,000.00 my fee is $50.00 plus 0.018% of sale priceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-71990638221937537682017-03-28T02:35:56.917+01:002017-03-28T02:35:56.917+01:00Assuming you just the value of A1 to appear if it ...Assuming you just the value of A1 to appear if it is 40 or more then the formula in B1 should be:<br />=IF(A1<40,A1&"*",A1)Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-86354689776854395982017-03-28T01:41:39.503+01:002017-03-28T01:41:39.503+01:00Hello, there!
I want someone to help me formulati...Hello, there!<br />I want someone to help me formulating for the following condition :<br />If A1 is less than 40, the value in A1 should return together with an asterisk. For example, 19 in A1 should appear in B1 as 19*.<br /><br />Thanks in advance!Chhabi Acharyahttp://www.blogger.com/profile/04852814382570438438noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-11692471252226838292017-03-19T11:35:17.438+00:002017-03-19T11:35:17.438+00:00Thanks , very helpful !Thanks , very helpful !Daniel Touhttp://www.blogger.com/profile/16018948335036780958noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-80149241807626263172017-01-26T11:09:28.459+00:002017-01-26T11:09:28.459+00:00Brilliant! so clear and concise and just helped on...Brilliant! so clear and concise and just helped on a deadline submission of a tender. Thank you so much for sharing your knowledge.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-64987400114815229662017-01-21T16:02:01.394+00:002017-01-21T16:02:01.394+00:00I don't understand the questionI don't understand the questionGlen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-78703079628785803092017-01-21T15:57:45.502+00:002017-01-21T15:57:45.502+00:00Sir i want fixed in cell about $0.00 Ex: A1= $0.00...Sir i want fixed in cell about $0.00 Ex: A1= $0.00 ( don't delete in cell except values )YeS.YeN.YeM. Srhttp://www.blogger.com/profile/05145383215943181140noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-53333554114731369982017-01-17T10:01:27.352+00:002017-01-17T10:01:27.352+00:00Just put the $ in front of the row rather than th...Just put the $ in front of the row rather than the column, i.e.<br /><br />E$1Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-1731982476731561412017-01-17T09:54:31.494+00:002017-01-17T09:54:31.494+00:00Hello Sir,
$ fix Column E1, E2, E3
If i want fix r...Hello Sir,<br />$ fix Column E1, E2, E3<br />If i want fix row E1, F1, G1.<br />Thank you. Amgad Abadirhttp://www.blogger.com/profile/09535009642437070227noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-27342768636102519842017-01-11T09:25:47.802+00:002017-01-11T09:25:47.802+00:00yeah, thanks for your post. I think i found out my...yeah, thanks for your post. I think i found out my problem, I have fixed and everything is ok now,andro dumpperhttp://appforpc1.com/androdumpper-wps-connect-download-for-pcwindow/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-31585754568462852762017-01-05T01:27:12.489+00:002017-01-05T01:27:12.489+00:00thanks a lot for your support. I will try itthanks a lot for your support. I will try itRichard Nicolehttp://www.jiotvapp.com/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-22837749861408856042017-01-04T06:32:31.916+00:002017-01-04T06:32:31.916+00:00You should be able to do what you are after using ...You should be able to do what you are after using a dynamic range. Open the Name Manager on the Formula ribbon, and add a new range (let's call it dropdown), and enter your formula as the range area. Dollar rules apply, so if you don't add the dollars, the reference will be relative to the currently active cell - for example if you have cell B2 selected and you enter =A2 as your range definition, your range will always be the cell to the left of the active cell. If you enter =$A2, the range will always be the cell in column A of the same row as the active cell, etc.<br />Once you have created the range, you just enter its name preceded by the equals sign as the list in your data validation, e.g. =dropdown.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-28468093340458228452017-01-04T04:01:44.276+00:002017-01-04T04:01:44.276+00:00Hi,many thanks for your post. I got one question:
...Hi,many thanks for your post. I got one question:<br /> for Data Validation -> List, as source, instead of 23 in '=$K$6:$K23', i want to give a formula, for example, I want to give something like '=$K$6:$K(VLOOKUP(C3,N2:P9,2))', is it possible? If so, what is the right format? thanks for any helpRichard Nicolehttp://www.jiotvapp.com/noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-63724158612106545522016-11-25T06:03:05.460+00:002016-11-25T06:03:05.460+00:00thnqthnqAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-87188224603363705802016-11-15T20:21:12.933+00:002016-11-15T20:21:12.933+00:00You can achieve the same thing with R1C1 referenci...You can achieve the same thing with R1C1 referencing by using the actual row and column numbers (no square brackets) rather than the relative square brackets you've used in your example. e.g.<br /><br />=R2C4<br /><br />is the same as<br /><br />=$B$4Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-30074962054145990372016-11-15T10:26:40.525+00:002016-11-15T10:26:40.525+00:00Does the dollar sign for fixing the collumn/row al...Does the dollar sign for fixing the collumn/row also work in row/collumn numericall reference style sheet ? (E. g. =R[-1]C+RC[-2]; newest version of office) I am afraid not...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-35511756948161464812016-10-04T06:49:04.396+01:002016-10-04T06:49:04.396+01:00If I've understood your problem correctly, you...If I've understood your problem correctly, you can't do it just with dollars as the you are wanting the column to increase with the row. The dollar would only allow you to specify whether the row increased with the row or not.<br /><br />I think OFFSET would be your simplest solution:<br /><br />Try<br /><br />=SUMPRODUCT(Sheet1!$B$2:$B$21,OFFSET(Sheet1!$M$2,0,ROW(A2)-2,20,1))<br /><br />We are basically using the row number of the current cell , ROW(A2)-2, to determine how many columns we should offset from M2.<br /><br />The OFFSET function is explained here:<br />http://www.notjustnumbers.co.uk/2010/02/using-offset-function-in-microsoft.html<br /><br />I hope that helps.<br />Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-11273915408174533982016-10-03T20:13:56.148+01:002016-10-03T20:13:56.148+01:00I have a problem and not sure how to solve it in e...I have a problem and not sure how to solve it in excel.<br />I want each cell along vertical column lets say column A to refer a calculation on two columns on a separate sheet and replace the second column moving towards right keeping first column fixed on each consecutive cell of the resulting sheet in the same column.<br />For example,<br /><br />cell A2 of sheet 2 = sumproduct(all of col b2:b21 of sheet 1, all of col m2:m21 of sheet 1)<br />I want cell A3 of sheet 2 = sumproduct( all of col b2;b21 of sheet 1, all of column n2:n21 of sheet 1)<br /><br />what would be the $ usage in the case above? Please suggest. Arieswonderhttp://www.blogger.com/profile/17007723596655414856noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-4917229211009434482016-08-31T14:24:04.573+01:002016-08-31T14:24:04.573+01:00Any idea how to use cell references in C#? Actuall...Any idea how to use cell references in C#? Actually there is no cell in C# i just need to know if there is a code equivalent.<br /><br />tyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-17225171147091017452016-06-07T12:46:50.523+01:002016-06-07T12:46:50.523+01:00Thank you sir. With your help finished the job as ...Thank you sir. With your help finished the job as planned.<br /><br />Thank youChengayihttp://www.blogger.com/profile/06854907671854354249noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-2555398107131331802016-06-02T07:31:45.319+01:002016-06-02T07:31:45.319+01:00thanks for the info..was helpful
thanks for the info..was helpful<br />PRASOON KUMAR Phttp://www.blogger.com/profile/08767105339210970645noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-49795998362528406552016-04-12T21:44:54.461+01:002016-04-12T21:44:54.461+01:00Thanks for asking the question, and thanks for the...Thanks for asking the question, and thanks for the answer, Glen.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-27750491731943974802016-03-07T18:54:51.842+00:002016-03-07T18:54:51.842+00:00How to have just a certain number that change when...How to have just a certain number that change when dragged down? e.g: 01/Q4/2015. How to have just 01 that change to 02, 03 and so on but Q4 and 2015 sill fix?Mega Herminhttp://www.blogger.com/profile/10443755978487815264noreply@blogger.com