Formula for comparing totals?

• Hi,

Im looking for a formula for the following:

I have a row of figures, and want a formula to enable the chosen cell to tell me if its plus or minus a certian number - for example, i have a row with numbers 5, 4, 3, 4, and i want to know whether its more, less or equall to 15, the cell chosen would say "+1", or if I was wanting to know if it was more, equall or less than 20, it would say "-4"

thanks very much for any help, much appreciated

• Re: Formula for comparing totals?

what would the formula be though? im not too hot with formulas - the real data i have is 5,2.5,2.75,0.5,0,0.25,0,1,1,2,1 and i want to know if its more or less than 16 - which I could just count, but im doing 65 rows, so will jsut copy the formula

cheers

• Re: Formula for comparing totals?

Try

IF(SUM(A1:A50)<>16,"+1","")

Amend the Range to suit your data.you could also use Conditional Formatting to apply Bold or Coloured Font to the result

• Re: Formula for comparing totals?

thats cool mate if it is only going to be one higher or lower than 16, but if its say for example 5 higher than 16, it will just say +1

any ideas?

cheers

• Re: Formula for comparing totals?

I don't quite follow what you want, but this formula will tell you how much below/over 16 the result is.

=IF(SUM(A1:A50)>16,"+" & SUM(A1:A50)-16,IF(SUM(A1:A50)<16,"-"&16-SUM(A1:A50),""))

• Re: Formula for comparing totals?

thats perfectly what I need,
cheers mate

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!