Forum Discussion
Solved
Greater than and less than functions
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 ind...
- 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
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
=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.