Numbers help me work out the gas consumption

numbers

Trying hard to work out my yearly gas and electricity consumption.

I have forumla for SUMIF:
=SUMIF (B,"="&F6,Gas Reading)

But I think I want to do (IF the year is 2015 [F6 entry], then find the MAX and MIN values – which I can then work out consumption with subtraction.

Can anyone help… my experimentation with MIN and MAX isn't working!

Many thanks

Best Answer

Presumably you mean that you want to compare each entry of B, so first you need to correct your SUMIF command to have a B:B rather than B. SUMIF(B:B,...) That will indicate the full B column.

However you seem to have the good idea of looking at the max and min readings on in 2015. In this case, you want to find a maximum among a range. In excel this is secretly doing two things: first, finding which cells happen to equal 2015 and picking those out. Then, find their maximum. To get the values for the Gas Reading when the year is 2015, you create a vector or "array" of values that are only the Gas Readings you want. This is the output of an if statement: IF(B:B=F6,C:C) which has the effect of looking at each entry of the B column, asking if it's equal to F6. If so, then return the corresponding element of the C column. Notice that it spits out a bunch of numbers not just one, so you can't just enter that expression above and see all the numbers. It's not a "function" (with one output).

You want the maximum of these numbers, minus the minimum of the same numbers, to get the amount of gas you used. Therefore the function you seek is =MAZ(IF(B:B=F6,C:C))-MIN(IF(B:B=F6,C:C)) Caveat!! Make sure to use control-shift-return. After you enter the formula, while your cursor is still in the function box, press "control-shift-return" rather than just return. This allows excel to deal with the array, i.e. all those values inside the max() function, all at once.

Related Solutions

Applescript read specific data out of iWork Numbers

this is how I solved it:

tell application "iTunes"

    repeat with theTrack in selection

        set theFile to location of theTrack
        tell application "Finder" to set theName to name of theFile

        set ayahInSurahCount to 0

        --set ayah information from the name of the track

        set surahNumAsString to characters 1 thru 3 of theName as text
        set surahNumber to surahNumAsString as integer

        set ayahNumberAsString to characters 4 thru 6 of theName as text
        set ayahNumber to ayahNumberAsString as integer
        -- finished

        set track number of theTrack to ayahNumber
        set genre of theTrack to "Genre"
        --getting information from the number sheet
        set aName to my getAName(surahNumber)
        --set englishName to my getEnglishName(surahNumber)
        -- finished
        set the album of theTrack to surahNumAsString & "-" & aName
        set artist of theTrack to "Abdul Basit"
        set shufflable of theTrack to false
        --set description of theTrack to englishName
        set name of theTrack to ayahNumberAsString & " - "

    end repeat
end tell

on getAName(surahNumber)
    tell application "Numbers"
        activate
        tell document 1
            tell sheet 1
                tell table 1
                    set ret to value of cell 1 of row surahNumber
                    --set value of cell 5 of row 1 to "Salam"
                end tell
            end tell
        end tell
    end tell
    return ret
end getArabicName

on getEnglishName(surahNumber)
    tell application "Numbers"
        activate
        tell document 1
            tell sheet 1
                tell table 1
                    set ret to value of cell 3 of row surahNumber
                end tell
            end tell
        end tell
    end tell
    return ret
end getEnglishName

Numbers: Make numbers negative

A workaround is to create a column with formulas like "=-1 * A1", then copy and "Paste Formula Results" (⇧⌘V). But I'm sure there are better solutions.

Best Answer

Related Solutions

Applescript read specific data out of iWork Numbers

Numbers: Make numbers negative

Related Question