Percentage flag

[soulmiester]

Hi all this is my first time here so please be gentle.
I have a basic sales board consisting of reps, days of the week, double sales and single sales, mandates and total. Each mandate can be a single or a double but what i need is a formula that will flag the mandates cell if the total doubles sales breaches 30% of the total mandates sold.
Example.
Steve on monday sells 8 lines, 3 doubles and 2 singles and does this monday to friday. This would give him 15 doubles and 10 singles, giving him 25 total mandates. The ratio from 15 doubles to 25 mandates, this represents 60% doubles sales.
What i need is a formula to insert into a new column that shows that percentage relation and for it to flag if it breaches 30%
I know this is long winded but its the only way i could explain what help i need.
Any and all help and advice would be appreciated.
Thank you.
Lee.

 

[soulmiester]

Fake names buddy. They may well be real but not to us lol.
Thanks for the heads up though.

 

[etaf]

so you can just multiply P or Q by 2 or divide by 2

so in column P which is Singles divide by 2
in P3 = =SUMIF($B$2:$O$2,$P$2,B3:O3)/2
and copy down
then everything else should work

 

[soulmiester]

Yer the singles are fine as they are, its Q that needs sorting, so line 5 should say 15 singles thats fine, but each double column would be say 2 as its 1 single mandate but has two sales on it, so Q would be 3 as in 3 doubles, making the percentage 14.28% of the total sales, so column T is correct
Does this make sense?
Lol

 

[soulmiester]

Ok if i just changed Q by dividing by 2 wouldn't that change the value of the total column?
the mandates column formula i had divided each doubles cell so when the total formula saw the value 2 in the doubles cell it automatically saw it as 2 but the mandates cell saw it as 1.
Ive a feeling ive massively over complicated it but the original formula worked for what i needed, all i needed was the inclusion of a singles to doubles percentage column.

so you can just multiply P or Q by 2 or divide by 2

so in column P which is Singles divide by 2
in P3 = =SUMIF($B$2:$O$2,$P$2,B3:O3)/2
and copy down
then everything else should work

 

[etaf]

i didn't touch your columns and as i had to copy into a spreadsheet i didn't use your formulas
Also the table in the post does not layout when copied into a spreadsheet

if your sheet is correct - then why not just use a percent as i posted earlier

( cell with the doubles in , however you wanted to measure) / total number - again however you want to add the total
that gives a %
then follow the conditional formatting
or put a spreadsheet onto a share - either dropbox or onedrive - I dont use unknown share site
and i can then add the formatting - but it would be worth you trying

 

[soulmiester]

ok I see what you have done now, you have made the percentage value of Q from the singles column, the calculation for the doubles percentage needs to be from the total column, so take line 5 for instance, he did 15 singles, and 3 doubles (reported as a 4 and a 2 in the doubles column. the percentage of double mandates is a percentage based on the total which is (21 Total), so 3 double mandates is 14.28% of 21
I hope im making sense.
Im so grateful for your help.
Lee

 

[soulmiester]

Ok so if i put the original excell in my dropbox and share the link with you, do you think you could sort it for me pretty please?

i didn't touch your columns and as i had to copy into a spreadsheet i didn't use your formulas
Also the table in the post does not layout when copied into a spreadsheet

if your sheet is correct - then why not just use a percent as i posted earlier

( cell with the doubles in , however you wanted to measure) / total number - again however you want to add the total
that gives a %
then follow the conditional formatting
or put a spreadsheet onto a share - either dropbox or onedrive - I dont use unknown share site
and i can then add the formatting - but it would be worth you trying

 

[soulmiester]

etaf, heres my spreadsheet original.

https://www.dropbox.com/s/ike77p8eeiglci8/Daily figures report sheet example.xlsx?dl=0

I cant thank you enough for this..

i didn't touch your columns and as i had to copy into a spreadsheet i didn't use your formulas
Also the table in the post does not layout when copied into a spreadsheet

if your sheet is correct - then why not just use a percent as i posted earlier

( cell with the doubles in , however you wanted to measure) / total number - again however you want to add the total
that gives a %
then follow the conditional formatting
or put a spreadsheet onto a share - either dropbox or onedrive - I dont use unknown share site
and i can then add the formatting - but it would be worth you trying

 

[etaf]

OK
So doubles percent is in column Q

and we sum the doubles =SUM((D4+F4+H4+J4+L4+N4+P4)
and divide by 2
=SUM((D4+F4+H4+J4+L4+N4+P4)/2

Now you want that as a Percent of the total Manadates
=( SUM((D4+F4+H4+J4+L4+N4+P4)/2 ) / SUM((D4+F4+H4+J4+L4+N4+P4)/2+(C4+E4+G4+I4+K4+M4+O4))

is that correct

OR divide by total
=( SUM((D4+F4+H4+J4+L4+N4+P4)/2 ) / =SUM(C4:P4)

As you have conditional formatting for RED FILL for blank cells
What colour FILL do you want the over 30% to go ? in column Q

 

[soulmiester]

OMG, you are awesome,
Its the total amount of double mandates, as a percentage of the TOTAL
Take line 6, this has on monday 15 singles and 1 double which is reported as (2) and Tuesday only 2 double mandates reported as (4), This gives 15 single mandates plus 3 double mandates = 18 mandates but 21 total sales, now (Q) needs to be the total amount of doubles reported (3) as a percentage of the total (S)

Oh and can the cell colour be orange please if it breaches 30%

OK
So doubles percent is in column Q

and we sum the doubles =SUM((D4+F4+H4+J4+L4+N4+P4)
and divide by 2
=SUM((D4+F4+H4+J4+L4+N4+P4)/2

Now you want that as a Percent of the total Manadates
=( SUM((D4+F4+H4+J4+L4+N4+P4)/2 ) / SUM((D4+F4+H4+J4+L4+N4+P4)/2+(C4+E4+G4+I4+K4+M4+O4))

is that correct

OR divide by total
=( SUM((D4+F4+H4+J4+L4+N4+P4)/2 ) / =SUM(C4:P4)

As you have conditional formatting for RED FILL for blank cells
What colour FILL do you want the over 30% to go ? in column Q