Simple formula

[LebD]

Hi
I am trying to have this formula working with one more criteria. Not sure why I am stuck:, initially this is working
=IFERROR(IF(LEN(VLOOKUP(C4;'Sheet2'!H:BD;5;FALSE))=0;0;VLOOKUP(C4;'Sheet 2'!H:BD;5;FALSE)/100);0)

But I need to add one more criteria, if vlookup returns >100, if true it should show 100 otherwise the rest of the formula above.

it is one more IF criteria to be inserted in the 3rd part of the formula above, theoritically like that but its not working

=IFERROR(IF(LEN(VLOOKUP(C4;'Sheet2'!H:BD;5;FALSE))=0;0;IF(VLOOKUP(C4;'Sheet 2'!H:BD;5;FALSE)/100>100;100;VLOOKUP(C4;'Sheet 2'!H:BD;5;FALSE)/100);0)

 

[LebD]

If vlookup returns -100, result should be zero 0
if vlookup returns nothing (N/A), result should be zero
if it returns 0, result should be zero
if it returns 100 and more , result should be 100
if it returns anything between 1 and 99, result should be equal to return

 

[ravi2628]

Can you please confirm the type of data(Number) or (percentage)

 

[LebD]

Can you please confirm the type of data(Number) or (percentage)

percentage

 

[Fluff]

When you say

if it returns 100 and more , result should be 100

Is that before or after the return value is divided by 100

 

[ravi2628]

percentage

Then you can try the post 6# it works perfectly

as I have given you with example

 

[Fluff]

If your values are already percentages, why are you dividing them by 100?

 

[LebD]

If your values are already percentages, why are you dividing them by 100?

cause otherwise its giving me 100% = 10000%, so i divide it.

@ravi2628 except for % between 1 and 99, it returns 100

 

[LebD]

Ok Now it works well

Thank you all

=IFERROR(IF(VLOOKUP(C2;'Sheet'!H:BD;5;0)=0;0;IF(VLOOKUP(C25;'Sheet'!H:BD;5;0)>100;"100%";VLOOKUP(C25;'Sheet'!H:BD;5;0)/100));0)

 

[ravi2628]

Means your data is not in percentage it is just as number in cell

 

[Fluff]

Glad you sorted it & thanks fro the feedback.