Jump to content

Need some help with a excel formula.

looney
By looney
in Operating Systems
 Share
Go to solution Solved by Guest,

=MODE.SNGL(IF(A1:A110<>0;A1:A110))

 

Leave the cell with CTRL+SHIFT+ENTER and then it should look like this: {=MODE.SNGL(IF(A1:A110<>0;A1:A110))}

I hope that was the right function, I have no clue about the english function names.

 

Specifically AVERAGEIF(A1:A17, "<>0")

 

There is no modeif afaik.

Posted

Hello, I have a large column of data that I want to get the modus from.

The normal modus of my dataset would be "0" but I need it to exclude "0" in the modus calculations.

 

 

Example data array:

 

0

0

0

0

0

0

0

0

1

1

1

2

2

2

2

3

3

 

In the example the modus would be 0, but I need it to exclude 0's so that the modus will be 2.

Respect the Code of Conduct!

>> Feel free to join the unofficial LTT teamspeak 3 server TS3.schnitzel.team <<

>>LTT 10TB+ Topic<< | >>FlexRAID Tutorial<< | >>LTT Speed wave<< | >>LTT Communies and Servers<<

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

5

Case: Corsair Carbdie 330R Motherboard: Gigabyte GA-970A-DS3P Asus Z97-A CPU: AMD FX-6300 i5 4690K 3.5 GHZ + 212 EVO GPU: ASUS GTX 760 DirectCUII Ram: Corsair Vengeance LP 8gb (2x4gb) HDD: Seagate Barracuda 1TB PSU: Corsair CX500M
Monitor: AOC Q2963pm 29'' 21:9 IPS Mouse: Mionix Naos 8200 Mousepad: Mionix Sargas 320 Headset: HyperX Cloud Keyboard: Corsair Gaming K70 RGBIKEA Headset/Headphone Holder
Link to comment
Share on other sites
Link to post
Share on other sites
Posted

5

Not sure if actually answering or trolling, since I have no expertise in this situation.

Main Rig: CPU: AMD Ryzen 7 5800X | RAM: 32GB (2x16GB) KLEVV CRAS XR RGB DDR4-3600 | Motherboard: Gigabyte B550I AORUS PRO AX | Storage: 512GB SKHynix PC401, 1TB Samsung 970 EVO Plus, 2x Micron 1100 256GB SATA SSDs | GPU: EVGA RTX 3080 FTW3 Ultra 10GB | Cooling: ThermalTake Floe 280mm w/ be quiet! Pure Wings 3 | Case: Sliger SM580 (Black) | PSU: Lian Li SP 850W

 

Server: CPU: AMD Ryzen 3 3100 | RAM: 32GB (2x16GB) Crucial DDR4 Pro | Motherboard: ASUS PRIME B550-PLUS AC-HES | Storage: 128GB Samsung PM961, 4TB Seagate IronWolf | GPU: AMD FirePro WX 3100 | Cooling: EK-AIO Elite 360 D-RGB | Case: Corsair 5000D Airflow (White) | PSU: Seasonic Focus GM-850

 

Miscellaneous: Dell Optiplex 7060 Micro (i5-8500T/16GB/512GB), Lenovo ThinkCentre M715q Tiny (R5 2400GE/16GB/256GB), Dell Optiplex 7040 SFF (i5-6400/8GB/128GB)

Link to comment
Share on other sites
Link to post
Share on other sites
Posted
  • Author

5

I had already tried that and it didn't work, I also tried "Schnitzel", "1058" and "17", I'm now testing "Egypt" now but I don't think that will work either. 

Respect the Code of Conduct!

>> Feel free to join the unofficial LTT teamspeak 3 server TS3.schnitzel.team <<

>>LTT 10TB+ Topic<< | >>FlexRAID Tutorial<< | >>LTT Speed wave<< | >>LTT Communies and Servers<<

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

Not sure if actually answering or trolling, since I have no expertise in this situation.

Which means you'd be well advised not to post that at all.

 

OP, asterisks in the cells? AVERAGEIF()?

"You have got to be the biggest asshole on this forum..."

-GingerbreadPK

sudo rm -rf /

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

Maybe you should use D functions?

 

Spoiler

CPU:Intel Xeon X5660 @ 4.2 GHz RAM:6x2 GB 1600MHz DDR3 MB:Asus P6T Deluxe GPU:Asus GTX 660 TI OC Cooler:Akasa Nero 3


SSD:OCZ Vertex 3 120 GB HDD:2x640 GB WD Black Fans:2xCorsair AF 120 PSU:Seasonic 450 W 80+ Case:Thermaltake Xaser VI MX OS:Windows 10
Speakers:Altec Lansing MX5021 Keyboard:Razer Blackwidow 2013 Mouse:Logitech MX Master Monitor:Dell U2412M Headphones: Logitech G430

Big thanks to Damikiller37 for making me an awesome Intel 4004 out of trixels!

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

Specifically AVERAGEIF(A1:A17, "<>0")

"You have got to be the biggest asshole on this forum..."

-GingerbreadPK

sudo rm -rf /

Link to comment
Share on other sites
Link to post
Share on other sites
Posted
 

Not sure if actually answering or trolling, since I have no expertise in this situation.

Maybe trolling, maybe not. (I'm trolling)

 

I had already tried that and it didn't work, I also tried "Schnitzel", "1058" and "17", I'm now testing "Egypt" now but I don't think that will work either. 

Well try ''Car'', that usually works for me.

Case: Corsair Carbdie 330R Motherboard: Gigabyte GA-970A-DS3P Asus Z97-A CPU: AMD FX-6300 i5 4690K 3.5 GHZ + 212 EVO GPU: ASUS GTX 760 DirectCUII Ram: Corsair Vengeance LP 8gb (2x4gb) HDD: Seagate Barracuda 1TB PSU: Corsair CX500M
Monitor: AOC Q2963pm 29'' 21:9 IPS Mouse: Mionix Naos 8200 Mousepad: Mionix Sargas 320 Headset: HyperX Cloud Keyboard: Corsair Gaming K70 RGBIKEA Headset/Headphone Holder
Link to comment
Share on other sites
Link to post
Share on other sites
Posted
  • Author

Specifically AVERAGEIF(A1:A17, "<>0")

Im already doing that for the average calculation of the array but I also need a modus :)

Respect the Code of Conduct!

>> Feel free to join the unofficial LTT teamspeak 3 server TS3.schnitzel.team <<

>>LTT 10TB+ Topic<< | >>FlexRAID Tutorial<< | >>LTT Speed wave<< | >>LTT Communies and Servers<<

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

Im already doing that for the average calculation of the array but I also need a modus :)

Ah I'm stupid. Apparently at 3:30 AM modus=mean. Hold on.

"You have got to be the biggest asshole on this forum..."

-GingerbreadPK

sudo rm -rf /

Link to comment
Share on other sites
Link to post
Share on other sites
Posted
  • Solution

=MODE.SNGL(IF(A1:A110<>0;A1:A110))

 

Leave the cell with CTRL+SHIFT+ENTER and then it should look like this: {=MODE.SNGL(IF(A1:A110<>0;A1:A110))}

I hope that was the right function, I have no clue about the english function names.

 

Specifically AVERAGEIF(A1:A17, "<>0")

 

There is no modeif afaik.

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

=MODE(IF(A1:A17<>0,A1:A17))?

"You have got to be the biggest asshole on this forum..."

-GingerbreadPK

sudo rm -rf /

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

=MODE.SNGL(IF(A1:A110<>0;A1:A110))

 

Leave the cell with CTRL+SHIFT+ENTER and then it should look like this: {=MODE.SNGL(IF(A1:A110<>0;A1:A110))}

I hope that was the right function, I have no clue about the english function names.

 

 

There is no modeif afaik.

Yeah I'm up too late.

"You have got to be the biggest asshole on this forum..."

-GingerbreadPK

sudo rm -rf /

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

=MODE(IF(A1:A17<>0,A1:A17))?

 

That only works as an array formula, so you have to enter the formula with CTRL SHIFT ENTER.

Link to comment
Share on other sites
Link to post
Share on other sites
Posted
  • Author

{=MODUS(ALS(Z1:Z2048<>0;Z1:Z2048))} Did the trick, thanks a lot guys!

Respect the Code of Conduct!

>> Feel free to join the unofficial LTT teamspeak 3 server TS3.schnitzel.team <<

>>LTT 10TB+ Topic<< | >>FlexRAID Tutorial<< | >>LTT Speed wave<< | >>LTT Communies and Servers<<

Link to comment
Share on other sites
Link to post
Share on other sites
Posted

That only works as an array formula, so you have to enter the formula with CTRL SHIFT ENTER.

I realize :P

"You have got to be the biggest asshole on this forum..."

-GingerbreadPK

sudo rm -rf /

Link to comment
Share on other sites
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing Operating Systems

×