User Profile
bwy1129
Copper Contributor
Joined Jul 18, 2021
User Widgets
Recent Discussions
Re: I Need help designing a LET() function properly.
This is why I come to you guys! I've been in IT for over 40 years. (I still remember Radio Shack Model I computers with cassette tape storage), but I've crammed my head so full of system "things" that I have forgotten most of my VBA techniques. (Of course, it can't be MY or San Andreas' fault, so let's blame it on Mr. Alzheimer.) That worked perfectly (after I added the final close_parenthesis). 😉982Views1like0CommentsI Need help designing a LET() function properly.
My problem revolves around creating a range, using variables provided by the LET() function, to create a proper range for a MAX() function. The variables are row numbers. Background: I weigh myself daily. My Workbook has a Dashboard sheet, a Meter sheet, a Lookup sheet, and many others (but only those three apply to my question/problem.) The daily weights are recorded on the Meter sheet. Each day involves adding another row for "today's" new data. The data records begin on Row 6. The current row for the day I'm writing this is 258. (IOW, today is the 253rd day of recording my weight.) The number 258 is saved in a cell on the Lookup sheet. That cell has been defined with the name RowIDnow. I need to create a cell on the Dashboard sheet that shows the statistical MAXIMUM weight ever. If the rows were static, the MAX() function would look like this: =Max(Meter!R6C4:R258C4) However, because the last row used changes every day, the MAX() function needs to receive a relative range that must be constructed and then inserted into the parenthesis. That is where the LET() function comes in. Here is my current LET() function, which is failing with an unrecognized syntax so that I can't even test it to discover my logic error. =Let( Last,RowIDnow, max(Meter!r6c4:r&format(Last)&c4) )Solved1.1KViews0likes4CommentsRe: Problem with Logic
Juliano-Petrukio, Thank you for responding. My data spreads like ivy over seven worksheets and there are several dozens more to the formulas than what I'm giving you in this spreadsheet (like how weight factors in, other health issues, recording meal nutrient and calorie numbers, exercise, sleep, and so much more). Out of all of that, I created a simplified Workbook consisting of just one Worksheet with just one week of data. The effort was tedious and lengthy, but worth it! By bringing the various variables together onto a single sheet, I discovered two things. First, I uncovered some errors, which, when corrected, made the data make a lot more sense to me. Second, I discovered the cause of the circular logic. It was indeed circular -- in my head, spinning around and around! I hadn't taken into account the fact that "Day 1" of tracking is very different logically than days 2, 3, 4..., 3658, etc. So, I solved my own dilemma, but I wanted you to see the data as you requested. I've tried to include as much documentation (or explanation, however you want to think about my comments) as possible. Enjoy! (If such a verb can be applied to spreadsheets.) Bruce Younggreen1.2KViews0likes1CommentProblem with Logic
I have a situation where there is, both logically and in reality, circular logic. Here is a hypothetical example: 1. Total all the uses of resource "x". Call this "Column T" because the total is for each day. 2. Test each result of using resource "x" to make sure the result stays within an upper and lower limit. 3. If a result occurs outside the limit, calculate by how much the result missed the target and save that result for future reference. 4. To get future uses of resource "x" more accurate, determine ratios of preapplication"x" : resource"x"used : postapplication"x"differencefromtarget. OK, that is difficult enough, but this is a biological process where "preapplication'x'" and "postapplication'x'" are pre-activity and post-activity measurements and "resource'x'" is the amount of biological "stuff" required to accomplish the activity. Because it is biological, there are many factors, thousands in fact, that are (a) unknown, like stress; (b) too interdependent to isolate and measure, like temperature and moisture and time between applications of resource"x" and time required to complete the activity all affecting other contributions to the activity thereby indirectly affecting the effectiveness of resource"x". For this reason, there are tables of "guesstimates" for resource"x" and the formulas for those guesstimates depend on column T. The circular logic is: calculating totals to record in column T affects the guesstimates, and calculating new guestimates affects the totals to record in column T. The only way I can think of to break the circle is to store the calculated totals real numbers rather than calculated results. Does Excel have the ability to look at just a formula's result and not at the formula as a reference to the result? In other words, is there a way to retrieve data from column T without that retrieval looking at the cells elsewhere in the workbook that contributed to column T's data?1.3KViews0likes3CommentsRe: IF function when reference cell is a blank Cell with Formula
Courtney93, here's why 0 works and "0" doesn't. To Excel, 0 is classified as a "Number" while "0" is classified as "Text" or "General". The value of number 0 is zero. The value of text 0 is 48. Thus the test IF(F11>40,F11-40,0) sees a character whose value at some level of logic is greater 40, so the test comes out TRUE. (It shouldn't, but for some reason, that is what is happening.) . However, when the [logical test] portion of your formula renders TRUE, then the [value_if_true] portion calculates. The calculation works only on number values, not text values. The calculation sees "nothing" minus 40 and puts -40 into G11 as a result.4.5KViews0likes1CommentUsing calculation results as values
I'm using a calculated value as a conversion factor, but if circumstances change at random, I need to start using a fresh conversion factor. Although I can design the cell containing the factor to change as needed, all the previously entered data converted by the factor will change, too. To get around this, I need the data, when entered, to be converted by a value instead of a calculation result. Then, if the conversion factor changes in the future, data input after will convert properly while data entered in the past remains unchanged. Practical application: When a diabetic person uses insulin to manage blood sugar, there is a number (the factor in question) called Insulin Sensitivy Factor (ISF). It represents how much glucose in the blood is reduced by 1 unit of insulin. It is established by dividing the person's weight by the total insulin administered in 1 day (TDD). Assuming the insulin prescribed for the day is neither too much nor too little, then if the person gains or loses weight, the insulin required for the day changes appropriately by the following formula: TDD = [current weight] * [ISF] However, if the TDD (or Total Daily Dose) is consistently not enough or consistently too much over a period of a few days, then the doctor changes the dosage prescription and the ISF must be re-established. In the spreadsheet, the [current weight] is stored in an array of daily information but the ISF is stored in a secure cell as used similar to a CONSTANT. If the ISS value gets changed, then ALL the TDD history changes too. Bad! Very Bad! So, each day, when the weight is recorded, the formula that calculates the TDD for that day must somehow convert the ISS value from the retrieved value from the ISS's formula to a numerical value as if entered from the keyboard, then applied to the TDD formula. Can this be done without writing a macro, just by using Excel features, settings, formulas, functions, notations, and/or shortcuts?782Views0likes1CommentRe: Creating conditional formatting in cells OTHER than the selected cell
Thanks to Riny_van_Eekelen for solving my issue. I couldn't see the trees for the forest. I was using ...$B$4... in my formula and he used $B4 in the sample formula he sent back. I've attached a screenshot of the spreadsheet so if anyone is curious what I was trying to resolve, you can see why I needed highlight where TODAY'S data was/is. In the screenshot, I've notated five areas. By the numbers, they are: 1. Pale straw-colored fields are data input fields. 2. Pale sky-colored fields are calculated results and end-of-data information most diabetics need to know. 3. Pale dusty rose-colored fields are warning type data and high alert messages. 4. Strong green-colored row is the start of the TODAY data. 5. The size of the spreadsheet that a single day's data occupies.2.3KViews0likes0CommentsCreating conditional formatting in cells OTHER than the selected cell
I'm a diabetic and am working on a spreadsheet to keep track of insulin needs. The data (both collection, ie: unlocked cells) and calculation (ie: locked cells) is spread across columns A:P, organized by date on "relative line" 1, and covers "relative lines" 1:11. Because so much acreage is being covered EACH DAY, it is easy to loose track of "today" data among all the other data. I thought that perhaps I could conditionally format an entire relative line 1 (ie: A1:P1) when the date in B1 = today(). Well, I can do that easily in cell B1, but doing it in cells A1:P1 (rolls eyes) or worse, in A1, B1, C1, D1, ..., or even worse still in A1, B1, C1, D1,... A12, B12, C12, D12... ... A4015, B4015, C4015, D4015... is a nightmare. Here is what I want to do, in the steps I've taken to attempt to do it: Step 1, select "Conditional Formatting" Step 2, select "Manage Rules" Step 3, select "In worksheet" Step 4, select the rule that I wish would work correctly Step 5, select "Edit rule" Step 6, edit the formula to: =if(cell("content",address(cell("row"),2,3))=today(),true,false) {Translation: if the contents of a cell on the same row as the currently selected cell, but in column B instead of the currently selected cell's column, contains a value equal to the value produced by the "TODAY()" function, then format the currently selected cell with the defined format.} Step 7, create the formatting for background color and text color to highlight the start of the current day's data. The problem is that Excel generates the error, "There's a problem with this formula. Not trying to type a formula? When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula: • you type: =1+1, cell shows: 2. To get around this, type an apostrophe ( ' ) first: • you type: '=1+1, cell shows =1+1 [ OK ] [ Help ] I've tried it with the beginning =. I've tried it without the beginning =. I've tried it with the beginning apostrophe, followed by the =. I've tried it with the beginning apostrophe, without the following =. I've tried it in a cell as a cell formula, completely outside of the Conditional Formatting Rules. What am I doing wrong?Solved2.7KViews0likes2Comments
Recent Blog Articles
No content to show