formula explained

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formula - explained

Formula - explained

resolvedResolved · High Priority · Version 2003

Tally has attended:
Excel VBA Intro Intermediate course

Formula - explained

OK maybe a dumb question.... i need to interpret the below formula as i dont think it is doing what it should:

=IF((AND($C4="Y",$D4="Y")),$I$34*$K4,IF(AND($C4="Y",$D4="n"),($I$34*K4),$K4))

My interpretation is :

If C4 and D4 = y, then the cell K4 should = I34*K4
If C4 = Y and D4 = N, then cell k4 should = I34*K4

Is my interpretation correct, the last K4 is the cell to put the answer in?

Thanks
Tally

RE: formula - explained

Hi Tally

Which cell is the formula in in your spreadsheet?

thanks
Amanda

RE: formula - explained

Hi Amanda

Its K4... its a spreadsheet i am picking up from some one else...

Thanks
Tally

RE: formula - explained

Hi Tally

I think that you will find that the formula will throw up a circular reference if the formula is in K4 and it is also referring to that cell.

Say for example if you have the formula in cell K5, this is what would happen:

If C4 and D4 = y, then the cell K5 should =I34*K4
If C4 = Y and D4 = N, then cell K5 should = I34*K4
Otherwise (in any other situation apart from those described above) enter what is in cell K4 into cell K5

I hope this helps.

Amanda


 

Excel tip:

LARGE and SMALL functions and their uses

Two of Excel's most common functions are the MAX and MIN functions which will display the largest (MAX) or smallest (MIN) value in a series. What if you need the 2nd or 3rd largest or smallest values instead of the largest or smallest?

The =LARGE(array,n) returns the nth largest value of a series.

The =SMALL(array,n) function returns the nth smallest value of a series.

In both functions, 'n' represents the order of the number you want to display. For example, putting in 2 as n will give you the second highest number; putting in 3 as n will give you the third highest number.

View all Excel hints and tips


Server loaded in 0.08 secs.