Excel greater than/lower than

stoke1863 · 17 Feb 2011 at 21:00

Hi guys just need help on a simple Excel function.

I need my formula to perform a sum if the value in the cell is less than 30

i also need to perform a different sum if the value of the sell is higher than 45

what is the best way to do this?

Grad · 17 Feb 2011 at 21:05

Something like

=if(a1<30,Sum(Range),Sum(Range))

Although what do you want to happen if the value is exactly 30?

MikeHunt79 · 17 Feb 2011 at 21:06

I think you may have to resort to a macro to do this...

Grad · 17 Feb 2011 at 21:08

I see your cheeky edit

Just use a nested If

=if(a1<30,Sum(Insert first Range here i.e. c1:c2),if(a1>45,sum(Insert second Range here i.e. d1:d2),""))

so if your value is in a1 and your ranges are c1:c2 and d1:d2

=if(a1<30,Sum(C1:C2),if(a1>45,sum(D1: D2),""))

where a1 is the cell containing the value you are looking at

stoke1863 · 17 Feb 2011 at 21:19

Grad said:
I see your cheeky edit

Just use a nested If

=if(a1<30,Sum(Insert first Range here i.e. c1:c2),if(a1>45,sum(Insert second Range here i.e. d1:d2),""))

so if your value is in a1 and your ranges are c1:c2 and d1:d2

=if(a1<30,Sum(C1:C2),if(a1>45,sum(D1: D2),""))

where a1 is the cell containing the value you are looking at

Thanks a lot , i think i got it right now.

of course my example was simplified but this is the forumla

=IF($D14<30000,SUM($D14*$C$8+$B$8),IF($D14<75000,SUM($D14+$D14*$C$10+$B$10),""))

i think thats correct

whitecrook · 17 Feb 2011 at 21:19

stoke1863 said:
Hi guys just need help on a simple Excel function.

I need my formula to perform a sum if the value in the cell is less than 30

i also need to perform a different sum if the value of the sell is higher than 45

what is the best way to do this?

=if(a1<30,do sum,(if(a1>45,do sum)))

something like that

Grad · 17 Feb 2011 at 21:21

stoke1863 said:
Thanks a lot , i think i got it right now.

of course my example was simplified but this is the forumla

=IF($D14<30000,SUM($D14*$C$8+$B$8),IF($D14<75000,SUM($D14+$D14*$C$10+$B$10),""))

i think thats correct

they are both less than signs, is that intentional?

stoke1863 · 17 Feb 2011 at 21:25

Grad said:
they are both less than signs, is that intentional?

i got it wrong by mistake

Grad · 17 Feb 2011 at 21:25

stoke1863 said:
i got it wrong by mistake

Cool

Hope its sorted now.