Support Solved Condensing Spreadsheet Formulas

Discussion in 'Technology' started by Amaury, May 15, 2015.

Thread Status:
Not open for further replies.
  1. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    So I've transferred our dog's seizure totals to a spreadsheet document from a text document to reduce the chance of errors happening--if I do make an error on, say, the number of mild seizures she had for April, all I have to do is change that instead of changing that and then all of the totals, as the spreadsheet will re-calculate since it's using a formula for the totals--and because of the way I break the totals into different parts, I can't figure out how to condense the formulas.

    As an example, these are pretty simple, right?

    upload_2015-5-15_17-35-43.png

    upload_2015-5-15_17-40-28.png

    upload_2015-5-15_17-42-17.png

    However, I have to use a long formula for the 12/19/2014-[current] and 9/17/2014-[current] ranges, as seen below:

    upload_2015-5-15_17-42-31.png

    upload_2015-5-15_17-42-46.png

    I can't just do ??:?? because I obviously don't want to include the previous total(s).

    If I try something like =SUM(B3:B6)+(B10)+(B13:B17) or =SUM(B3:B6)+B10+(B13:B17), I get a value error:

    upload_2015-5-15_17-44-53.png

    upload_2015-5-15_17-45-30.png

    Any help?

    (@Mixt, you helped me when I having problems finding the average of times with formulas when there was a mix of AM and PM; maybe you can help me here, too! :D)
     
  2. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Honestly, as long as you are sure it works, I don't see much problem with big ugly formulas. All you normally see is the result and some of the fun things you can do will just never look pretty (I'm looking at you IF() function)

    Anywho. There are two big things you are missing that I can see.

    First, in that last image you have a range without a function. You have "=SUM(B3:B6)+B10+(B13:B17)" but functions end when the parentheses next to it closes, so it has no idea what you are trying to do with (B13:B17). If you need to use it again you need to say that again, so you want "=SUM(B3:B6)+B10+SUM(B13:B17)"

    Second, you can have more terms than you would ever want to use in a SUM() function. To separate them you just use a semicolon so "=SUM(2;5;7)" would display 14. Using this we can further condense the formula used above by dropping those plus signs and only calling the function once. Do that and you have, "=SUM(B3:B6; B10; B13:B17)"


    EDIT: On a second look you can also make your life easier because the ranges you are summing are already done on the sheet. So you could use "=SUM(B7; B11; B18)"
     
    Last edited: May 15, 2015
  3. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    For the first method, does it matter, whether another formula follows it or not, if the lone number is put in parentheses or not?

    • =SUM(B3:B6)+(B10) or =SUM(B3:B6)+B10
    • =SUM(B3:B6)+(B10)+SUM(B13:B17 or =SUM(B3:B6)+B10+SUM(B13:B17

    Both ways work, but can't hurt to ask.

    For the second method, are you using B3 (from my screenshots) as an example with that formula or was that just a random example?
     
  4. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    I made a small edit above if you want to check that out

    It doesn't matter if you put a lone cell in parentheses because that is mathematically valid. It just doesn't help because it will never do anything to that value.

    My examples were from B22, as shown in your last screen shot. Well except for the 2+5+7=14 thing. That is just because it helps sometimes to see numbers in the functions rather than cell references.
     
  5. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    Is your edit saying to add 93, 106, and 148? That would make a total of 347 for B22? Wouldn't it just be 93 and 148, which equal 241, or 106 and 135, which also equal 241?
     
  6. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Sorry, I'm sleepy any don't know your spreadsheet well. I'm just saying that you can reference the cells where you've already summed things together instead of doing it again. Though while it is cleaner you risk doing stuff like I did (adding stuff twice, adding something that isn't relevant in both groups, failing to notice you didn't add something, etc.)

    In the end as long as it makes sense to you, and it works, use whatever style you like.
     
  7. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    Well, it is 11:57 PM for you. Get to bed if you're tired. :D

    Thank you, Mixt.

    Edit: Does the semi-colon mean add, by the way?
     
    Last edited: May 16, 2015
  8. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    The semicolon is a separation of terms in a function. Since SUM() adds all of its terms together it will basically mean the same as + in this case. But in any other function no.
     
  9. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    And the same goes for commas, I take it, which can also add individual items? It just depends on what you're using: addition, subtraction, averaging, you name it.

    Now, this is kind of off topic, but it's related to spreadsheets, and I don't want to make a new thread for it.

    When it comes to time, if you want to round it, it's the same as with the numbers. If your time were 12:29:24 PM, you'd round to 12:29 PM because your seconds are 29 or less (0-29), but if your time were 12:29:30 PM, you'd round to 12:30 PM because your seconds are 30 or more (30-59).

    Regarding the activity log project I've got going on--the one you helped with a while ago--I was told that for the averages if I hid the seconds, they would automatically round, but that is not the case. The average here, for example, is 12:17 AM because the answer before rounding is 12:16:51 AM, which I've inputted manually as well as all the other roundings, but if I hide the seconds, it still displays 12:16 AM and doesn't change to 12:17 AM.

    It's not so much that I'm lazy and can't just continue doing the roundings manually, which is very easy, but I'd just like a formula there. I've tried using the =ROUND formula (something like =ROUND(506)) to no avail.

    upload_2015-5-15_22-39-59.png
     
  10. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Excel uses comas, Open Office uses semicolons.

    I'll have to back to you on time code rounding since that behaves oddly. If worse comes to worse I'm sure I can write one for you, though it may end up being a bit unwieldy.
     
  11. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    @Mixt, did you get a chance to look into the rounding formula?
     
  12. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    I don't have OpenOffice on my computer right now, but I think MROUND() will do the trick. I just don't know if the formula will take a time code. Try something like "=MROUND(A1; 0:01)" failing that "=MROUND(A1; 1/1440)" should work. Obviously swap A1 with the cell or value you actually want to round.
     
  13. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    Okay, so the first one gives me an error:

    upload_2015-5-24_19-54-31.png

    The second one, however, works:

    upload_2015-5-24_19-55-6.png
     
  14. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Cool. Just a brief rundown in case you want to modify the use later.

    MROUND() rounds the first term to the nearest multiple of the second term. So =MROUND(14; 5) would result in 15. And it supports decimals and fractions and stuff so =MROUND(3.45; 0.2) would be 3.4.

    Time codes are a number of days (It gets the date by starting at 1900's New Year). Then it gets hours and minutes from the decimal. So 0.5 would be noon, because that is half a day.

    So combing those two you can round to time intervals. There are 1440 minutes in a day so rounding to the nearest 1/1440 is the same as rounding to the nearest minute. You could round to the nearest hour using 1/24 or round to the nearest 15 minutes with 15/1440 or 1/96.
     
  15. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    Since we're here, I know what =MROUND(A1; 1/1440) says now thanks to your explanation above, so what does =IF(HOUR(D26)>12;D26-1;D26) basically say? (It's the formula you provided me quite a while ago to use to find the averages of my last activities when there's a mix of AM and PM.)
     
  16. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Okay, if I'm remembering correctly that was used because of something like the spreadsheet providing incorrect answers because it thought that the PM start times were after the AM end time.

    So the IF() function works with three terms. The condition (think of it as a yes or no question), then statement (what do you want if the answer was yes), and the else statement (what do you want if the answer was no). HOUR() simply returns what was in the hour slot of the supplied timecode.

    So we asked if the hour in the time code is above 12. If it is we subtract 1. If it wasn't we just wanted to use the time code as it is.

    As explained earlier, the whole number part of a time code is the number of days, and the decimal part is the time. By subtracting one we keep the time the same, but move it to the previous day; thus avoiding the error.
     
  17. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    Yup, you remembered correctly. If the times are all AM or all PM, I can just use =AVERAGE(A2:A8)--changing the values accordingly, of course. However, it's different when there's a mix of AM and PM because, as you said, it doesn't take into account that you're trying to find the average between the AM(s) of the current day and the PM(s) of the previous day. A more complex formula needs to be used--in this case, the IF--in a different column, formatting the times as numbers (anything at and after 12:00 AM displays as a regular time, but something like 10:31 PM displays as -01:29:00 instead) and then average those instead of just the times (e.g., =AVERAGE(B10:B16)

    Using a portion of my log below, if I just used =AVERAGE(), I would get 1:26 PM instead of 11:43 PM. That's why when I did it manually by hand, I had to use military time in reverse order. As an example, for 11:45 PM I would equate 45 / 60 + 11 instead of 45 / 60 + 23; for 1:32 AM I would equate 32 / 60 + 13 instead of 32 / 60 + 1. According to @Ienzo, it's to do with the fact that there'd be too big of a gap if we used 23 and 1.

    Week 3:

    Date Last Activity
    3/16/2014 3/17/2014 at 12:57 AM
    3/17/2014 3/17/2014 at 10:58 PM
    3/18/2014 3/18/2014 at 11:16 PM
    3/19/2014 3/20/2014 at 12:12 AM
    3/20/2014 3/20/2014 at 11:14 PM
    3/21/2014 3/21/2014 at 11:06 PM
    3/22/2014 3/23/2014 at 12:18 AM
     
  18. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    @Mixt, another quick question.

    I stopped this because it just seemed too complicated for such a basic thread and because it was getting tedious doing it manually. However, for my own personal use in the spreadsheet, is there a formula I could use to find out how many times I've used a time for a certain column?

    In this case, column D holds the individual daily times and column G holds the weekly average times (rounded). I know =COUNT can be used to see how many times in general have been used, but is there a formula, again, I could use to find out how many times, say, 11:32 PM has been used in column D and how many times, say, 1:40 AM has been used as the average in column G?

    Log.PNG
     
  19. Mixt The dude that does the thing

    Joined:
    Oct 18, 2006
    Gender:
    Male
    826
    Can't be bothered to test how it works with time codes right now, but the function you want is COUNTIF()

    The syntax on this one can get strange, but luckily you want an easy version of it. COUNT(range; value). Range being the cells to count, and value being what you want to match. If putting in the time directly doesn't work you could use a reference cell to get a time code. (e.g. COUNTIF(D1:D600; H1) where H1 contains 11:32 or whatever)
     
    Last edited: Jul 17, 2015
  20. Amaury Chaser

    Joined:
    Jan 15, 2007
    Gender:
    Male
    Location:
    Ellensburg, WA
    1,694
    Might want to put that second formula in [PLAIN][/PLAIN]. It got converted into a smiley and I had to switch to the basic editor. :D

    Seems I got it to work with your second method (see below). I was getting errors trying to the first one, but I may have been doing it wrong.

    Sheet 1.png

    Sheet 2.png
     
    Last edited: Jul 17, 2015
Thread Status:
Not open for further replies.