Formula value error

[Mel]

Hi,

I have the following formula in cell F3

=IF(D3="Y",C3/2,IF(D3="N",C3,IF(D3="","")))

this formula is copied down from F3 to F25

I have the following formula in cell G3

=IF(OR($F$26>499,B3="D"),F3*10%,)

this formula is copied down from G3 to G25

Cells D3 to D25 can contain a "N" or "Y" or must be empty.

As soon as cell F26 get an amount higher than 499 all the cells under the
one go to an error value?

Can anyone help please??

Thank you
Mel

 

[Roger Govier]

Hi Mel

Assuming that all the values in column C are numeric, the problem is caused
when a Null is returned in column F.

Change
=IF(D3="Y",C3/2,IF(D3="N",C3,IF(D3="","")))
to
=IF(D3="Y",C3/2,IF(D3="N",C3,0))

If you don't want to see the zeros showing, you could chose
Tolls>Options>general>untick Zero values

 

[muddan madhu]

replace the formula in cell F3

=IF(D3="Y",C3/2,IF(D3="N",C3,IF(D3="",0,0)))

 

[Mel]

Thank you both very much.

Both work, I tried one 0 but not the two.

I appreciate your help.

Warm Regards
Mel

 

[Mel]

Another problem with what I have done?

with this formula

=IF(OR($F$26>499,B3="D"),F3*10%,)

If a "D" appears in B3, I want F3*10% regardless of the value in F26, so if
there is 100 in F26 or 1000 in F26 provided a "D" appears in B3 it will give
the 10%.in the cell with the formula.

Thanks
Mel

 

[muddan madhu]

=IF(B3="D",F3*10%,"")

Another problem with what I have done?

with this formula

=IF(OR($F$26>499,B3="D"),F3*10%,)

If a "D" appears in B3, I want F3*10% regardless of the value in F26, so if
there is 100 in F26 or 1000 in F26 provided a "D" appears in B3 it will give
the 10%.in the cell with the formula.

Thanks
Mel

 

[Roger Govier]

Hi Mel

That's exactly what the formula does!!
What problem are you getting?

 

[Mel]

Hi,

Sorry, late reply. I live in Melbourne Australia and apart from the 45
degree Cel heat yesterday it was after midnight and I thought I had best get
sleep and look at it again after being refreshed. For some reason one cell
would not give the 10% even though I thought I had a D in the correct cell.
I looked at the formatting, same as other cells, looked at the formula, did
I miss something, re-put in all the info and it now seems to be working??.
Maybe I did not have a D, I un-ticked the 0 box in Tools, returned them, but
could not work out what I had done.

Anyway, I will continue to put in the data and see if other sheets also have
any problems.

You are probably asleep now, never know where people are except for the
extension to emails. e.g. uk presume England?
My hubby and I went to a wonderful place, Minack Theatre, near lands end
just last June.

Many thanks.
Mel

 

[Roger Govier]

Hi Mel

Glad you seem to have it sorted out.
Yes, I am in the UK in Monmouthshire in Wales.
Just heading off to be here 23:15 in UK.

If you do run into more problems with this workbook, you are welcome to mail
me a copy and I will take a look.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.