0

I am trying to create a calculated column based on this:

If statement from excel

where, L6 is a yes/no column, C6 is a number column and the lookup basically calculates the percentage based on another column, let's say column B from SharePoint (choice, 1-6).

This way, the lookup basically multiplies in the initial IF C63/53*10% if [Column B] is 1, 20% if it's 2 and so on.

IF [Column B]=1,0.10,0 IF [Column B]=2,0.20,0 IF [Column B]=3,0.30,0

Both conditions must be true for this to happen, so if L6 is False, the answer should be 0. Otherwise, if the L6 column is true, it multiplies the values with the percentage, depending on the value selected from [Column B].

Please let me know how I can be more specific for you to be able to help me in this matter.

Many thanks in advance!

1

A boolean column is a TRUE/FALSE value so

=IF( [L6]=True , "" , "" )

is exactly the same as

=IF( [L6] , "" , "" )

And...

Since a TRUE value equals 1 and a FALSE value equals 0
You can use a Boolean in a calculation

You state: so if L6 is False, the answer should be 0

Then your calculation can be:

=[L6] * [C6] / 53 * [Column B] * .1

If you are going to display the values you might want to control the number of significant digits:

=TEXT( [L6] * [C6] / 53 * [Column B] * .1 , ".000" )

Update #1

OP mentions the percentages for [Column B] can't be calculated.

You can use the CHOOSE function (example for values 1 to 5)

CHOOSE( [Column B] , .1 , .2 , .35 , .45 , .5 )

new Formula:

=[L6] * [C6] / 53 * CHOOSE( [Column B] , .1 , .2 , .35 , .45 , .5 )
  • Thank you for the reply, Danny. The only issue is that Column B has a different percentage to be used in the formula, depending on the selection. So for 1, it's 10%, for 2 - 20%, for 3 it's 35%, etc. – Alexandru Cirdei Nov 9 '15 at 17:30
0

If column B is a choice that a user has to make, then you don't have to do any logic on it as they are supplying the value for you. The calculation is 10% of the value entered so it could be written as (untested):

=If([L6]=True,[C6]/53*.1*[Column B]*.1,0)
  • Thanks for your time Eric. As I told Danny as well, the issue is that for each value, the percentage is different. So if he picks 2, it will be 20%, for 3 it will be 35% and so on :) – Alexandru Cirdei Nov 9 '15 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.