More than, less than formula

[Mel]

Hi,

I have a formula =IF(SUM(E30:G30)>10,E30+F30+G30,"") But now I need to add
to this that if sum of E30:G30 is more than(>) 6 but less than(<)10 then
(E30+F30+G30) to be divided by 2.

Any help appreciated.

Regards
Mel

 

[Pete_UK]

Try this:

=IF(SUM(E30:G30)>10,SUM(E30:G30),IF(SUM(E30:G30)>6,SUM(E30:G30)/2,""))

Hope this helps.

Pete

 

[Don Guillett]

You don't say what happens below 7
=SUM(E3:G3)/IF(SUM(E3:G3)>10,1,2)
so nothing if 6 or less
=IF(SUM(E3:G3)<=6,"",SUM(E3:G3)/IF(SUM(E3:G3)>10,1,2))

 

[Mel]

Thank you for that, they both seem to work OK. I have a slightly more
complex problem on another area of the sheet.

I have work it out like this
=IF(SUM(S32:U32)+SUM(P32:R32)>49,SUM(P32:U32)/2,IF(SUM(S32:U32)+SUM(P32:R32)>39,SUM(P32:U32)/4,"
")) but this formula gives me half of sum of all.

But if SUM(P32:U32) is greater than 49 what I need is for the cell value to
be SUM(P32:R32) plus half the SUM(32:U32) as the resulting value.
if SUM(P32:U32) is greater than 39 but less than 50 what I need is for the
value to be SUM( P32:R32) divided by 2 and the SUM( S32:U32) to be divided
by 4 then both to add together.
if the sum of P32:U32 is less than 40 then " "


e.g. if sum of P32:R32 is 5, and sum of S32:U32 is 46, then the sum of
P32:R32 will equal whatever that sum is i.e. 5 in this example, but the sum
of S32:U32 will be divided by 2, i.e. 23 both added together so resulting
value would be 5 + 23 equals 28



However,
e.g. if sum of P32:R32 is 4, and sum of S32:U32 is 40, then the sum of
P32:R32 will be half so 2 in this example, but the sum of S32:U32 will be
divided by 4, i.e. 10 both added together so resulting value would be 2 + 10
equals 12

I hope I have explained it OK.


Then I am also wondering if there is a way to take a $ value and get it
broken down to the notes/coin required for cash.

e.g. is I have to pay $285.75 then it might look like this
$285.75 5 under the $50 column, 1 under the $20 column, 1 under the $10
column, 1 under the $5 column, 1 under the 50cent column and 1 under the
..20cent column and 1 under the .5cent column.

Thanks
Mel

 

[Don Guillett]

You should be able to figure this out from the previous response. It may be
of interest to know that
=sum(p32:u32) is the same as SUM(S32:U32)+SUM(P32:R32)
Part 2 is also doable. Contact me privately and I will send you a file I use
for postage stamps that you can adapt.