Formula

S

sitelbanat

Board Regular

Joined

Oct 17, 2005

Messages

152

• Dec 8, 2005

• #2

How do i change my formula to get the inverse using MINVERSE to subtract the inverse of E69:E82?


=COUNTIF(O69:O82,">0")+SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82)

 

Upvote 0

G

GlennUK

Well-known Member

Joined

Jul 8, 2002

Messages

11,871

Office Version

1. 2019
2. 2013

Platform

1. Windows

• Dec 8, 2005

• #3

Excuse me but what are you talking about when you say inverse using MINVERSE? How can you have an inverse matrix of a range of cells that is 1 row x 13 columns?

 

Upvote 0

S

sitelbanat

Board Regular

Joined

Oct 17, 2005

Messages

152

• Dec 8, 2005

• #4

Well, I don't need to use MINVERS, butI thought It would be the right onw. All I need is to Subtract the inverse of the total.

=COUNTIF(O69:O82,">0")+SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82)


so whatever the total is for E69:E82 subtract the inverse of that total from the O69:O82 total count.

 

Upvote 0

S

sitelbanat

Board Regular

Joined

Oct 17, 2005

Messages

152

• Dec 8, 2005

• #5

so for example if the total of E69:E82 equals 0.8 I would like the formula to subtract 0.2

 

Upvote 0

G

GlennUK

Well-known Member

Joined

Jul 8, 2002

Messages

11,871

Office Version

1. 2019
2. 2013

Platform

1. Windows

• Dec 8, 2005

• #6

The inverse of 0.8 is 1.25 , so can you explain how you get 0.2?

 

Upvote 0

S

sitelbanat

Board Regular

Joined

Oct 17, 2005

Messages

152

• Dec 8, 2005

• #7

well meybe I am using the wrong word. For example


the count between O69:O82 equals 5
then in E70 the cell has a value of 0.8 the total would be 4.8, because I want to subtract the 0.2, thats wat I ment by inverse I could be using the wrong word am sorry

 

Upvote 0

S

Seti

Well-known Member

Joined

May 19, 2002

Messages

2,916

• Dec 8, 2005

• #8

Is this it?

=COUNTIF(O69:O82,">0")-(1-SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82))

 

Upvote 0

NBVC

Well-known Member

Joined

Aug 31, 2005

Messages

5,828

• Dec 8, 2005

• #9

Are you looking for something like this?

=COUNTIF(O69:O82,">0")+SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82)-SUM(IF(O69:O83>0,1/E69:E83))

evaluated with CTRL+SHIFT+ENTER

If not, please provide the data you have and the result you desire.

 

Upvote 0

S

sitelbanat

Board Regular

Joined

Oct 17, 2005

Messages

152

• Dec 8, 2005

• #10

=COUNTIF(O69:O82,">0")-(1-SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82))

This is so far is the closes one, but not quite right. For some reason it only counts to 4 all though there is 5 values between O69:O82, but then it is subtracting the opposite of the total in E Column, so at least I am half way. All I need is for it to count the right values.


O69 = 2356
O72 = 2366
O75 = 5456
O80 = 5478
O81= 4578

Then add the opposite

E 69 = 0.8 since there is a value in O69


the answer should be 4.8 because I want to add the opposite of 0.8 to the total count

 

Upvote 0