Assigning a value to a cell.

Robert_Kroschel

New Member
Joined
Mar 28, 2018
Messages
8
I've been kicking this one around for a long time now. I would like to assign a numeric value to a cell based on its position in a table or row.
I would then like to total the value of each respective cell content.

Capture.PNG


Ignore Columns H - RS3
A number will be placed in the first column S1 I would like to assign it the highest value rate of 1
Another number will be placed in S2 and I would like to assign a value rate of 0.5
And another number will be placed in Column S3 and it will be assigned a rate value of .25
The same Pattern applies for Cells RS1 - RS3 and also R1-R3.
I then want to count the number that appears the most amount of times while also totalling its overall rating from the three column groups

Now considering all the other columns lets say the no 16 appears 5 times across the entire row, its count is 5 but its value is dependant on where it appeared in the three main column groups
So if 16 was first in column group S it is assigned a value of 1 then further more 16 appeared in the second column In the RS column group it would be assigned a value of 0.5 (totalling 1.5 at this point) if the no 16 appeared in the 1st column of the R Group it would be assigned a value of 1. (totalling 2.5)
The value 16 is an arbitrary value so hence could be text I suppose. It is not a fixed value.

So in this example 16 would end up with count value of 5 and rating value of 2.5

I hope someone can help me with this challenge
.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,956
Office Version
  1. 365
Platform
  1. Windows
Based on the screen capture I'm going to assume that the 3 groups of numbers are in B2:D2, N2:P2 and R2:T2

Count
Excel Formula:
=SUMPRODUCT((B2:D2=MODE(B2:D2,N2:P2,R2:T2))+(N2:P2=MODE(B2:D2,N2:P2,R2:T2))+(R2:T2=MODE(B2:D2,N2:P2,R2:T2)))
Value / score
Excel Formula:
=SUMPRODUCT((B2:D2=MODE(B2:D2,N2:P2,R2:T2))+(N2:P2=MODE(B2:D2,N2:P2,R2:T2))+(R2:T2=MODE(B2:D2,N2:P2,R2:T2)),{1,0.5,0.25})
 

Robert_Kroschel

New Member
Joined
Mar 28, 2018
Messages
8
Based on the screen capture I'm going to assume that the 3 groups of numbers are in B2:D2, N2:P2 and R2:T2

Count
Excel Formula:
=SUMPRODUCT((B2:D2=MODE(B2:D2,N2:P2,R2:T2))+(N2:P2=MODE(B2:D2,N2:P2,R2:T2))+(R2:T2=MODE(B2:D2,N2:P2,R2:T2)))
Value / score
Excel Formula:
=SUMPRODUCT((B2:D2=MODE(B2:D2,N2:P2,R2:T2))+(N2:P2=MODE(B2:D2,N2:P2,R2:T2))+(R2:T2=MODE(B2:D2,N2:P2,R2:T2)),{1,0.5,0.25})
Thanks Jason75 , your a smarter man than me :) I will give it a go and modify it as necessary and see how it pans out.
 

Robert_Kroschel

New Member
Joined
Mar 28, 2018
Messages
8
Thanks Jason that worked well, but my ranges were different.
and I updated it to use MODE.SNGL
=SUMPRODUCT((B3:D3=MODE.SNGL(B3:D3,K3:M3,O3:Q3))+(K3:M3=MODE.SNGL(B3:D3,K3:M3,O3:Q3))+(O3:Q3=MODE.SNGL(B3:D3,K3:M3,O3:Q3)),{1,0.5,0.25})

Same for the count formula.

Now I am wondering if you can help me with the next level of complexity .
I probably didn't explain a major factor and that is that in each of the 3 ranges the number can only appear once. IE No 2 cant be in the first second or third range more that once.

For example in Range 1 you can not have the value like this 2,6,2 nor in any other of the 3 ranges . It can only appear like this
Range 1 [1,2,5] Range 2 [1,5,2] Range 3 [2,1,6]

This is where it gets really complex. I have tried the Mode.Mult but with now success.

I need to know the in the instance where their are equal counts of a value in the vertical array the value assigned to each so they can be differentiated.

IE Range 1 [2,1,3] , Range 2 [1,3,2] Range 3 [1,2,6]
1 and 2 appear the same amount of times , but in different locations.
in the above instance No 1 would rank higher with a value of 2.5 and No2 wold rank second With a value of 1.75
The No 3 would rank 3rd with 1.5 and No6 last with a value of 0.25

Complex challenge ... :)
Capture.PNG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,956
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm not going to have much chance to look at this in detail until tomorrow (Friday) evening, but noting that your arrays are horizontal, not vertical, it might just be a case of using TRANSPOSE with MODE.MULT (based on the tooltip that comes up when you start typing the formula).

You will need to transpose each of the Mode.Mult functions in the formula individually with 3 closing brackets before each + symbol instead of 2.

If you are using anything other than an up to date version of office 365 then you will most likely need to array confirm the formula with Ctrl Shift Enter.
 

Robert_Kroschel

New Member
Joined
Mar 28, 2018
Messages
8
I'm not going to have much chance to look at this in detail until tomorrow (Friday) evening, but noting that your arrays are horizontal, not vertical, it might just be a case of using TRANSPOSE with MODE.MULT (based on the tooltip that comes up when you start typing the formula).

You will need to transpose each of the Mode.Mult functions in the formula individually with 3 closing brackets before each + symbol instead of 2.

If you are using anything other than an up to date version of office 365 then you will most likely need to array confirm the formula with Ctrl Shift Enter.
Thanks Jason, I really appreciate your help , yes I read about the transpose and will apply it , I am using the latest version of office so I should be able to get away with out the array entry key combination
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,956
Office Version
  1. 365
Platform
  1. Windows
With the multiple results, how would you want them displayed on the sheet? First attempt at solving it below. The order appears to be based on the original data rather than anything in the results, although sorting should be possible, it will need to be set based on final requirement.

Copy of Dates WIP.xls
BCDKLMOPQSTU
3213132126231.75
4132.50
Sheet1
Cell Formulas
RangeFormula
S3:S4S3=MODE.MULT(B3:D3,K3:M3,O3:Q3)
T3:T4T3=SUMPRODUCT(($B$3:$D$3=S3)+($K$3:$M$3=S3)+($O$3:$Q$3=S3))
U3:U4U3=SUMPRODUCT(($B$3:$D$3=S3)+($K$3:$M$3=S3)+($O$3:$Q$3=S3),{1,0.5,0.25})
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,270
Messages
5,657,749
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top