If Function

[jmahlow7]

I need Help, I Have 3 Sales People who I want to calculate their Percentages on their deals.

So if A1 =

Jessica than I want B1 to = 20
Daniel than I want B1 to = 40
Olivia than I want B1 to = 40


Also, If I continue to add more sales people at different percentages, whats your suggestions on a better way to do this formula? Or do I always have to go in here and add new people to the formula?

 

[Aladin Akyurek]

In B1 enter:

=IFERROR(VLOOKUP(A1,{"Jessica",20;"Daniel",40;"Olivia",40},2,0),0)

 

[jmahlow7]

In B1 enter:

=IFERROR(VLOOKUP(A1,{"Jessica",20;"Daniel",40;"Olivia",40},2,0),0)

Thanks! Works Perfectly!

 

[mamilinko]

Alternatively for many sales people, you can create a constant mapping range somewhere else in the sheet or in another sheet, lets say you create your mapping in range F1 to G3, you enter F1=Jessica G1=20, then F2=daniel G2=40 and so on, you only once create this mapping range and whenever you add more pople add to buttom of the mapping range, then go to B column and call with a vlookup this way, vlookup(A1, F1:G3,2,0) copy down formula , thats all you will get correct result

 

[Aladin Akyurek]

Thanks! Works Perfectly!

You are welcome.

 

[jmahlow7]

Alternatively for many sales people, you can create a constant mapping range somewhere else in the sheet or in another sheet, lets say you create your mapping in range F1 to G3, you enter F1=Jessica G1=20, then F2=daniel G2=40 and so on, you only once create this mapping range and whenever you add more pople add to buttom of the mapping range, then go to B column and call with a vlookup this way, vlookup(A1, F1:G3,2,0) copy down formula , thats all you will get correct result

Makes Total Sense.I am not familiar with Mapping, so I will see if I can make it work, I have another sheet that I have been using for making lists

 

[jmahlow7]

Also If I have a function such as F4 =F2-(D2*100), since its subtracting and If I have no data in there it will always be -100 until I put data. How can I make it show nothing until data is entered?

 

[jmahlow7]

Is there also an easier way to use this formula for the full column instead of dragging it down 1000 rows?

 

[Aladin Akyurek]

Is there also an easier way to use this formula for the full column instead of dragging it down 1000 rows?

Perhaps something like...

=IF(ISNUMBER(F2)*ISNUMBER(D2),F2-(D2*100),"")