SOLVED

Greater than and less than functions

%3CLINGO-SUB%20id%3D%22lingo-sub-3429356%22%20slang%3D%22en-US%22%3EGreater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429356%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20cell%20(J4)%20to%20calculate%20a%20range%20of%20cells%20(E4%3AE73)%20and%20show%20the%20result%20in%20cell%20(J4).%3C%2FP%3E%3CP%3EIf%20the%20individual%20cells%20are%20greater%20than%201.5%20it%20needs%20to%20add%20the%20value%20of%20leftover%20amount%2C%20also%20if%20the%20individual%20cells%20are%20less%20than%201.5%20I%20need%20it%20to%20subtract%201.5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help%20you%20provide%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3429356%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429436%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1401832%22%20target%3D%22_blank%22%3E%40Izzy0803%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(IF(E4%3AE74%26gt%3B1.5%2CE4%3AE74-1.5%2CE4%3AE74-1.5))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20try%20this%20formula.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20943px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F375041i4BA8500C4DC92E78%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20alt%3D%22greater%20or%20less%20than%201%2C5.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429534%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429534%22%20slang%3D%22en-US%22%3EThat%20works.%20The%20only%20problem%20I%20have%20now%20is%20I%20cant%20get%20it%20to%20ignore%20the%20blanks.%20If%20I%20enter%20%22%20%22%2C%20there%20are%20to%20many%20arguments%20for%20the%20function%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429573%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429573%22%20slang%3D%22en-US%22%3EI%20am%20wondering%20if%20the%20cell%20is%20%26lt%3B%201.5%20is%20it%20supposed%20to%20subtract%201.5%20from%20the%20calculation%20OR%20subtract%201.5%20from%20the%20cell%20value%20and%20add%20the%20difference%20to%20the%20calculation%3F%20If%20it%20is%20the%20latter%20as%20is%20what%20the%20above%20solution%20appears%20to%20give%20then%20can't%20you%20just%20do%3A%3CBR%20%2F%3E%3DSUM(%20range%20)%20-%20COUNT(%20range)*1.5%3CBR%20%2F%3Ewhich%20by%20the%20way%20will%20ignore%20spaces.%3CBR%20%2F%3EIf%20it%20is%20the%20former%20then%20you%20need%20something%20like%3A%3CBR%20%2F%3E%3DSUMPRODUCT(%20ISNUMBER(range)*(%20(range%26gt%3B1.5)*(range%20-%201.5)%20-%20(range%26lt%3B1.5)*1.5))%3CBR%20%2F%3EBTW%20what%20should%20happen%20if%20the%20value%20is%20exactly%201.5%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429581%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429581%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1401832%22%20target%3D%22_blank%22%3E%40Izzy0803%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(IF(NOT(ISBLANK(E4%3AE18))%2CIF(E4%3AE18%26gt%3B1.5%2CE4%3AE18-1.5%2CE4%3AE18-1.5)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20try%20this%20formula%20which%20seems%20to%20exclude%20blank%20cells%20in%20my%20sheet.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20958px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20958px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20958px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20958px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20958px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20style%3D%22width%3A%20958px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F375057iF07B5D9B1B8519CB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20alt%3D%22values%20greater%20or%20less%20than%201%2C5.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429589%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429589%22%20slang%3D%22en-US%22%3EIt%20is%20to%20subtract%20from%20the%20cell%20value%20and%20if%20it%20is%201.5%20it%20can%20be%20ignored%20too.%20I%20am%20calculating%20%22bank%20time%22%20for%20our%20pilots.%20They%20must%20fly%201.5%20on%20average%20for%20each%20period%20they%20fly.%20So%20if%20they%20fly%201.3%20it%20would%20be%20-0.2.%20Also%20if%20they%20flew%202.4%20it%20would%20add%200.9%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429622%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429622%22%20slang%3D%22en-US%22%3Eso%20did%20you%20try%20%3DSUM(%20range%20)%20-%20COUNT(%20range)*1.5%3CBR%20%2F%3Eor%20if%20you%20are%20really%20just%20interested%20in%20the%20Average%20then%20why%20not%3A%3CBR%20%2F%3E%3DAVERAGE(%20range%20)%3CBR%20%2F%3Eor%20the%20difference%20from%20the%20target%20average%3A%3CBR%20%2F%3E%3D%20AVERAGE(%20range%20)%20-%201.5%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3429755%22%20slang%3D%22en-US%22%3ERe%3A%20Greater%20than%20and%20less%20than%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3429755%22%20slang%3D%22en-US%22%3E%3DSUM(%20range%20)%20-%20COUNT(%20range)*1.5%3CBR%20%2F%3EWORKS!%3CBR%20%2F%3EI%20really%20appreciate%20the%20help%20and%20knowledge%3C%2FLINGO-BODY%3E
New Contributor

I need a cell (J4) to calculate a range of cells (E4:E73) and show the result in cell (J4).

If the individual cells are greater than 1.5 it needs to add the value of leftover amount, also if the individual cells are less than 1.5 I need it to subtract 1.5

 

 

Thank you for any help you provide

7 Replies

@Izzy0803 

=SUM(IF(E4:E74>1.5,E4:E74-1.5,E4:E74-1.5))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

greater or less than 1,5.JPG 

That works. The only problem I have now is I cant get it to ignore the blanks. If I enter " ", there are to many arguments for the function
I am wondering if the cell is < 1.5 is it supposed to subtract 1.5 from the calculation OR subtract 1.5 from the cell value and add the difference to the calculation? If it is the latter as is what the above solution appears to give then can't you just do:
=SUM( range ) - COUNT( range)*1.5
which by the way will ignore spaces.
If it is the former then you need something like:
=SUMPRODUCT( ISNUMBER(range)*( (range>1.5)*(range - 1.5) - (range<1.5)*1.5))
BTW what should happen if the value is exactly 1.5?

@Izzy0803 

=SUM(IF(NOT(ISBLANK(E4:E18)),IF(E4:E18>1.5,E4:E18-1.5,E4:E18-1.5)))

You can try this formula which seems to exclude blank cells in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

values greater or less than 1,5.JPG 

It is to subtract from the cell value and if it is 1.5 it can be ignored too. I am calculating "bank time" for our pilots. They must fly 1.5 on average for each period they fly. So if they fly 1.3 it would be -0.2. Also if they flew 2.4 it would add 0.9
best response confirmed by Sergei Baklan (MVP)
Solution
so did you try =SUM( range ) - COUNT( range)*1.5
or if you are really just interested in the Average then why not:
=AVERAGE( range )
or the difference from the target average:
= AVERAGE( range ) - 1.5
=SUM( range ) - COUNT( range)*1.5
WORKS!
I really appreciate the help and knowledge