IF + value THEN calculation

[lavezzi7]

Hi guys,

I am trying to set up a formula that would automatically subtract 20% (from 100%) for any given value of the given cells:

Example:

100 %

A X
B
C X
D
E

In this case, cells B, D and E do not have any value so the percentage should be 60 %.

Thanks in advance!

 

[JustynaMK]

Hi Lavezzi,

Assuming that your data is in cells A1:A5, for example:
A1 - 80
A2 - empty
A3 - 70
A4 - empty
A5 - empty

Then you can paste the following formula into cell B1 & drag it down:
=IF(LEN(A1),A1*0.8,0.6)

Is this what you were looking for?

 

[lavezzi7]

Thanks for the answer but not exactly like that.

Let's assume I want to calculate the completeness of a 5 member team. Full team is 100 % completeness.

When a player is injured the completeness decreases by 20 %, for instance if 4 players are injured team completeness will be 100 % - (4 * 20 %) = 20 %.

I am also trying with LEN but cannot figure it out yet.

 

[lavezzi7]

Fixed with Counta function

 

[JustynaMK]

awesome!
P.S. =LEN will simply check if a cell is not blank.