Explain these please

[Sal]

Hello you techies out there. I have been asked to explain the following -
can anybody make clear to me so I acn in turn explain to others. Thank you.
=IF('TL Table'!A6="","",IF('TL Table'!A6="(blank)","",'TL Table'!A6))

=IF(B3="",IF(D3="","",J2),RIGHT(B3,10))

=IF(A4="","Z",VLOOKUP(LEFT(A4,4),TEAMS!A:D,3,FALSE))

=COUNTIF(P$4:P$19,$A24)

=ROUND(L20*$C20*7*(1-$C$27),0)

ROUND(H45*$C45*7*(1-($C68+$C69)),0)+(H54*$C54*7*(1-$C69))

I would be grateful for any help.

Kind regards.
Sal

[Gary Brown]

Get into Excel
Press F1
Type...
IF worksheet function then
select Search
Read the answer
Do the same thing for vLookup, Right, CountIf and Round.

the 'TL Table'!A6 syntax means, in cell A6 of the worksheet called TL Table.
HTH,
Techie


"Sal" wrote:

> Hello you techies out there. I have been asked to explain the following -
> can anybody make clear to me so I acn in turn explain to others. Thank you.
> =IF('TL Table'!A6="","",IF('TL Table'!A6="(blank)","",'TL Table'!A6))
>
> =IF(B3="",IF(D3="","",J2),RIGHT(B3,10))
>
> =IF(A4="","Z",VLOOKUP(LEFT(A4,4),TEAMS!A:D,3,FALSE))
>
> =COUNTIF(P$4:P$19,$A24)
>
> =ROUND(L20*$C20*7*(1-$C$27),0)
>
> ROUND(H45*$C45*7*(1-($C68+$C69)),0)+(H54*$C54*7*(1-$C69))
>
> I would be grateful for any help.
>
> Kind regards.
> Sal
>
>
>
>
>
>

[DCSwearingen]

The IF function is really IF, THEN, ELSE

=IF('TL Table'!A6="","",IF('TL Table'!A6="(blank)","",'TL Table'!A6))

If 'TL Table' Cell A6 is "" (null) then return "" (null), otherwise check to see if it is blank. If it is blank return "" (null), if it is not null or blank, return the value in A6. This almost seems redundant, unless it is used in a system that returns "(blank)" for a null value. I am not familiar with the use of "(blank)".

*******************
=IF(B3="",IF(D3="","",J2),RIGHT(B3,10))

If cell B3 contains a value (is not null) then return the right ten characters of text in cell B3. Else if cell B3 is null, check cell D3. If it is also null, then return null. However, if B3 is null and D3 contains a value, return the value J2.

*******************
=IF(A4="","Z",VLOOKUP(LEFT(A4,4),TEAMS!A:D,3,FALSE))

If cell A4 is null return the letter "Z". If it is not null scan down the array in columns A through D in the TEAMS spreadsheet. Compare the left four characters in cell A4 with the first column in the array. If a match is found, return the value in the third column in the array.

The false arguement says to find an exact match. If the arguement is true or omitted, then the function will return the value in the third column that corresponded to the last alphabetical (can be numerical) entry that comes close to matching the left four characters in cell A4.

*******************
=COUNTIF(P$4:P$19,$A24)

Count all values in the range P4 through P19 that match the value in cell A4.

The $ ahead of a row number will 'lock' the row in the formula if the cell is copied to another row. The $ ahead of a column letter will lock the column if the formula is copied to another column. This is relative and absolute referencing. Without the $ the referencing is relative to the location of the cell with the formula and coping will maintain that relative address.

*******************
=ROUND(L20*$C20*7*(1-$C$27),0)

The ROUND function with the zero says to round the result to zero decimal places.
So the value in cell L20 is multiplied by the value in cell C20, the result is multiplied by seven, and that result is multiplied by the result of subtracting the value of cell C27 from one (Remember, just like high school algebra, do the calculations in the inner parenthesis first. Subtracting a percentage from one is a common method of applying a discount.) The product of all of these multiplications is then rounded to the nearest whole number. Again, the dollar sign is used to lock rows or columns when the formula is copied to another location.

*******************
ROUND(H45*$C45*7*(1-($C68+$C69)),0)+(H54*$C54*7*(1-$C69))

Again, start with the innermost parenthesis ($C68+$C69) add those values and sumtract from one (this customer must get two discounts?) Like the last example multiply the value in H45 by the value in C45 and multiply that result by seven. Apply the discount in the first inner calculation and round to the nearst whole number.

That result is held until the next set of calculations is computed.

Again, the innermost parethesis is one minus the value in C69 for the discount. Multiply the value in H54 by the value in C54, multiply this product by seven, and apply the discount to the result.

It is interesting to note that this result is NOT rounded. The first set of calculations must have one set of rules for discounts, while the last set of calculations must have different rules for discounts.

Finally add the result of the first set of calculations to the result of the second set of calculations to get the final result.


I hope I approached this the way you wanted?