Sum function

[MINDSTORM77]

Hi Guys,

I am trying to work out a sumif type function for an excel app that i need to update.

Basically i have cell B4&B5 which have a user input as below for example,

B4 = £20,000
B5 = £15,000

Given a set of macro parameters, cell B14 will display the mean of these two figures - £17,500

Depending on another user cell input of vehicle age however, sometimes, B14 will be a reduced figure which is less than mean

I want a cell to display either "0" or "No" if the mean is correct, otherwise display the mean less the value in cell B14

The example i give is;

B4 = £20,000
B5 = £15,000

B14 displays a reduced rate of £13,500 now so i want my new call to display £1,500 (£17,500-£13,500)

My initial formula was =IF(SUM(B4:B5)/2-B14=SUM(B4:B5),"0",SUM(B4:B5)/2-B14)

This displays the reduced rate ok, but just displays the mean as well if there is no reduced rate instead of the 0 or preferably the word No ....

I understand formatting the cell to display either a currency or general alphabet is probably not possible so will have to settle for 0

Any help is greatly appreciated in advance.

 

[etaf]

how about

IF( SUM(B4:B5)/2 = B14, "No", (SUM(B4:B5)/2)-B14 )

I want a cell to display either "0" or "No" if the mean is correct

Given a set of macro parameters, cell B14 will display the mean of these two figures

So the macro - will put Mean in B14
therefore if
Them mean of B4 & B5 = B14 - then nothing entered to reduce the value and so
the mean is correct

I want a cell to display either "0" or "No" if the mean is correct,

and so
SUM(B4:B5)/2 = B14
will be TRUE
and NO should display

IF
SUM(B4:B5)/2 = B14 is FALSE
then

otherwise display the mean less the value in cell B14

(SUM(B4:B5)/2)-B14

(SUM(B4:B5)/2) = Mean

 

[MINDSTORM77]

Hi, Thank you for the reply, there must be a cell formatting issue as i get the result as displayed.
in this example, the result displayed should be £1,500 (mean of B4 & B5 - B14)

If B7 or B8 are more favourable, the output for cell B14 will be the same as the mean between B4 & B5 hence the new cell i have added will just display £0 or No whichever is possible.

 

[Joe4]

Hi, Thank you for the reply, there must be a cell formatting issue as i get the result as displayed.
in this example, the result displayed should be £1,500 (mean of B4 & B5 - B14)

If B7 or B8 are more favourable, the output for cell B14 will be the same as the mean between B4 & B5 hence the new cell i have added will just display £0 or No whichever is possible.

Did you forget to put the equal sign (=) at the beginning of your formula?
Without it, you are just entering text and not a formula into the cell.

All those formulas in ETAF's post should be prefaced by equal signs (as all Excel formulas are required to do), i.e.

=IF(SUM(B4:B5)/2=B14,"No",(SUM(B4:B5)/2)-B14 )

 

[MINDSTORM77]

Hi All,

Again, thank you for your informative responses.

Appears to be working like a charm now! + rep to both of you!

Just being cheeky here, if B14 returns £0, is there any way to add the work No in this scenario too?

Forgot to mention if B8 is a low figure compared to the mean of B4:B5, the output of B14 will always be £0

 

[etaf]

Just being cheeky here, if B14 returns £0, is there any way to add the work No in this scenario too?

=IF( OR(SUM(B4:B5)/2 = B14, B14=0) , "No", (SUM(B4:B5)/2)-B14 )

 

[MINDSTORM77]

Perfect, that is the ticket right there!

 

[etaf]

you are welcome