Anyone here know excel?

500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
6/24/2016 8:27pm
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
|
hvaughn88
Posts
8363
Joined
6/19/2013
Location
Conway, AR US
6/24/2016 8:39pm Edited Date/Time 6/24/2016 8:40pm
500guy wrote:
How can I divide this formula by 5? =SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5) also if I have a # like 22.856489 how do I round down to just 2 decimal...
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
Order of operations. After your "=" put the entire formula in parenthesis, then /5. There should be a decrease decimal button up by your font box at the top
500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
6/24/2016 8:47pm
Thank you!
ToolMaker
Posts
5964
Joined
11/19/2011
Location
Escondido, CA US
Fantasy
927th
6/24/2016 9:00pm
Also you can format the cell to only 2 decimal places I think
500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
6/24/2016 9:05pm
ToolMaker wrote:
Also you can format the cell to only 2 decimal places I think
I found one deal

=Rounddown then the cell and number of decimals Vaughns worked much easier.

The Shop

71Fish
Posts
1794
Joined
11/29/2011
Location
Ogden, UT US
6/24/2016 10:02pm
Right click the cell then format cell.
sostoked
Posts
246
Joined
3/26/2015
Location
Twentynine Palms, CA US
6/24/2016 10:27pm
I'd rather go door to door selling dingleberries than do an excel spreadsheet.

Thanks for asking though.
kzizok
Posts
8392
Joined
10/19/2010
Location
AS US
Fantasy
1859th
6/25/2016 8:48am
500guy wrote:
How can I divide this formula by 5? =SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5) also if I have a # like 22.856489 how do I round down to just 2 decimal...
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
Im gonna steal that one! Classic!
scooter5002
Posts
4761
Joined
6/6/2010
Location
Nanton Alberta CA
6/25/2016 10:39pm
Use an Excel spreadsheet for my books, and while it's only for trucking, it works great. Once I figured it out, just easy peasy. Tally everything up, e-mail the file to my accountant at year end. Sure beats pen and adding machine. Lol
IWreckALot
Posts
8676
Joined
3/12/2011
Location
Fort Worth, TX US
6/26/2016 1:15pm
What does the small function do? I use excel all day every day at work. just havent come across the small function ever. I have an overcomplicated if formula that is probably in the thousands of characters long at this point. Just a bunch of if statements stacked on top of each other. Probably easier if I set the formula to a vlookup at this point.
DPR250R
Posts
2128
Joined
9/14/2006
Location
NJ US
6/27/2016 6:59am
500guy wrote:
How can I divide this formula by 5? =SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5) also if I have a # like 22.856489 how do I round down to just 2 decimal...
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
Don't beat yourself up over that. There are a thousand ways to get to the same answer.

What is most important is , if you are questioned or are questioning yourself, that you FULLY understand the expression you wrote. I have seen people copy expressions/formulas from the web and they have no idea what is going and just pray that the answer they are giving is correct.

Since you are using it everyday things will start to come easier and easier to you.

If you want.... Post some sample dummy data and what you are trying to achieve... Maybe we can help out.
500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
6/27/2016 9:31am
IWreckALot wrote:
What does the small function do? I use excel all day every day at work. just havent come across the small function ever. I have an...
What does the small function do? I use excel all day every day at work. just havent come across the small function ever. I have an overcomplicated if formula that is probably in the thousands of characters long at this point. Just a bunch of if statements stacked on top of each other. Probably easier if I set the formula to a vlookup at this point.
in my case, I was trying to post 10 scores then grab the lowest 5 or "SMALLEST" then divide by 5 to get the average.

I agree with the above you really need to check the answers because when I was trying different things I was getting answers I knew were wrong but did not know why.
IWreckALot
Posts
8676
Joined
3/12/2011
Location
Fort Worth, TX US
6/27/2016 12:02pm Edited Date/Time 6/28/2016 2:48am
IWreckALot wrote:
What does the small function do? I use excel all day every day at work. just havent come across the small function ever. I have an...
What does the small function do? I use excel all day every day at work. just havent come across the small function ever. I have an overcomplicated if formula that is probably in the thousands of characters long at this point. Just a bunch of if statements stacked on top of each other. Probably easier if I set the formula to a vlookup at this point.
500guy wrote:
in my case, I was trying to post 10 scores then grab the lowest 5 or "SMALLEST" then divide by 5 to get the average. I...
in my case, I was trying to post 10 scores then grab the lowest 5 or "SMALLEST" then divide by 5 to get the average.

I agree with the above you really need to check the answers because when I was trying different things I was getting answers I knew were wrong but did not know why.
I see that now. I'm usually dealing with various IF statements like SUMIF(S), and COUNTIF(S). Probably stuff easier done in a pivot table but I'm more comfortable using formulas when I can.

The formula I use that I should convert to a vlookup formula looks at a cell and determines what to display depending on if a cell contains a number of different values.

Here is the formula I've written. I'll probably pull this in a bit since it's technically work material but I'm just using it for reference. Rather than writing a million "IF" statements, I probably should create a new table that has each available value, then reference that table with a VLookup. When I started this, it was only 5 or 6 IF statements, but as time has gone on, the statements grow more and more. Oh and I just did a character count and it's 5,490 characters long. Yeah that's probably bigger than it should be.
6/27/2016 6:06pm
IWreckALot wrote:
I see that now. I'm usually dealing with various IF statements like SUMIF(S), and COUNTIF(S). Probably stuff easier done in a pivot table but I'm more...
I see that now. I'm usually dealing with various IF statements like SUMIF(S), and COUNTIF(S). Probably stuff easier done in a pivot table but I'm more comfortable using formulas when I can.

The formula I use that I should convert to a vlookup formula looks at a cell and determines what to display depending on if a cell contains a number of different values.

Here is the formula I've written. I'll probably pull this in a bit since it's technically work material but I'm just using it for reference. Rather than writing a million "IF" statements, I probably should create a new table that has each available value, then reference that table with a VLookup. When I started this, it was only 5 or 6 IF statements, but as time has gone on, the statements grow more and more. Oh and I just did a character count and it's 5,490 characters long. Yeah that's probably bigger than it should be.
That is one heck of a formula! Are those declined charges or something? and a reason why?
IWreckALot
Posts
8676
Joined
3/12/2011
Location
Fort Worth, TX US
6/27/2016 6:14pm
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that there is some data element missing from the transaction. The cardholder would never know what happened and the merchant rarely notices.
IWreckALot
Posts
8676
Joined
3/12/2011
Location
Fort Worth, TX US
6/27/2016 6:16pm
We have to leave a note in the accounts saying what happened and what we did with the transaction in case the merchant calls customer service asking about it. The formula generates the log note so the rep dealing with the transaction doesn't have to manually type a note into the account.
6/27/2016 6:33pm
So is cell G2 the error type? I use vlookups for reconciliations of 3rd party payments... They have a list of clients they're paying for and I have an invoice on my end and when they don't match, a vlookup easily pulls out those that are missing... Just trying to understand how it would help you
DPR250R
Posts
2128
Joined
9/14/2006
Location
NJ US
6/27/2016 6:38pm Edited Date/Time 6/27/2016 6:44pm
IWreckALot wrote:
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that...
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that there is some data element missing from the transaction. The cardholder would never know what happened and the merchant rarely notices.
That is the longest nested IF statement I have ever seen.... lol Do you know how to use Access or another type of database software? That is where I would head with this one.

Sticking to Excel, I would do what you said. Create a reference table for your reason codes. I would then break apart your codes into partial descriptions. Finally, use a combination of VLOOKUP and TEXT to bring it all back together.





Also. you may want to look into making your own VBA Functions. Specifically, a "Select Case" statement. Basically an easier way to read IF statements. As you can see, nested IFs can get tricky.

http://www.techonthenet.com/excel/formulas/case.php

http://www.excel-easy.com/vba/examples/select-case.html

Hopefully this helps get you going in a more sustainable direction as your spreadsheet grows...



IWreckALot
Posts
8676
Joined
3/12/2011
Location
Fort Worth, TX US
6/27/2016 6:41pm
500guy wrote:
How can I divide this formula by 5? =SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5) also if I have a # like 22.856489 how do I round down to just 2 decimal...
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
Yes, g2 is the error code.
6/27/2016 6:47pm
IWreckALot wrote:
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that...
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that there is some data element missing from the transaction. The cardholder would never know what happened and the merchant rarely notices.
DPR250R wrote:
That is the longest nested IF statement I have ever seen.... lol Do you know how to use Access or another type of database software? That...
That is the longest nested IF statement I have ever seen.... lol Do you know how to use Access or another type of database software? That is where I would head with this one.

Sticking to Excel, I would do what you said. Create a reference table for your reason codes. I would then break apart your codes into partial descriptions. Finally, use a combination of VLOOKUP and TEXT to bring it all back together.





Also. you may want to look into making your own VBA Functions. Specifically, a "Select Case" statement. Basically an easier way to read IF statements. As you can see, nested IFs can get tricky.

http://www.techonthenet.com/excel/formulas/case.php

http://www.excel-easy.com/vba/examples/select-case.html

Hopefully this helps get you going in a more sustainable direction as your spreadsheet grows...



This
IWreckALot
Posts
8676
Joined
3/12/2011
Location
Fort Worth, TX US
6/27/2016 6:50pm
IWreckALot wrote:
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that...
They are transaction reject codes. They're not declined reasons in the sense that your transaction gets rejected at a store. It's more of an issue that there is some data element missing from the transaction. The cardholder would never know what happened and the merchant rarely notices.
DPR250R wrote:
That is the longest nested IF statement I have ever seen.... lol Do you know how to use Access or another type of database software? That...
That is the longest nested IF statement I have ever seen.... lol Do you know how to use Access or another type of database software? That is where I would head with this one.

Sticking to Excel, I would do what you said. Create a reference table for your reason codes. I would then break apart your codes into partial descriptions. Finally, use a combination of VLOOKUP and TEXT to bring it all back together.





Also. you may want to look into making your own VBA Functions. Specifically, a "Select Case" statement. Basically an easier way to read IF statements. As you can see, nested IFs can get tricky.

http://www.techonthenet.com/excel/formulas/case.php

http://www.excel-easy.com/vba/examples/select-case.html

Hopefully this helps get you going in a more sustainable direction as your spreadsheet grows...



Haha. Yeah it's gotten a bit out of control. I need to dive into vba and just learn the syntax. I'm in my final two semesters of my IT degree so once I've graduated, I'll probably spend some time figuring it out. I have 2 programming courses next semester and an advanced programming course in the spring and I'll be graduated finally.
DPR250R
Posts
2128
Joined
9/14/2006
Location
NJ US
6/27/2016 6:59pm
IWreckALot wrote:
Haha. Yeah it's gotten a bit out of control. I need to dive into vba and just learn the syntax. I'm in my final two semesters...
Haha. Yeah it's gotten a bit out of control. I need to dive into vba and just learn the syntax. I'm in my final two semesters of my IT degree so once I've graduated, I'll probably spend some time figuring it out. I have 2 programming courses next semester and an advanced programming course in the spring and I'll be graduated finally.
That is great... congrats! If you can... take up some SQL classes as well (unless you already know the language).
500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
7/1/2016 7:43am
New question

I keep track of monthly parts expenses I use 3 things DATE VENDOR and AMOUNT

at the end of the month I go Alt-N-V-T and make a pivot table , this works good most times but I noticed the totals are wrong some times. any clues on what I might be doing wrong ?
DPR250R
Posts
2128
Joined
9/14/2006
Location
NJ US
7/1/2016 10:46am
500guy wrote:
How can I divide this formula by 5? =SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5) also if I have a # like 22.856489 how do I round down to just 2 decimal...
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
Are you sure you are getting all the data in there?

When selecting your source data... are you selecting entire columns?

If you add data after creating the table you need to possibly re-size and refresh your source.

Also... how are you entering data? Typing straight into the cells?Are you using any type of data validation or forms? Its important that your dates, numbers are being entered as the correct data types. True dates and true numbers.

or are you pulling from some type of source? Accointing program etc...

Typing from phone... Sry for typos
500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
7/1/2016 11:38am
500guy wrote:
How can I divide this formula by 5? =SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5) also if I have a # like 22.856489 how do I round down to just 2 decimal...
How can I divide this formula by 5?

=SMALL(D2:D11,1)+SMALL(D2:D11,2)+SMALL(D2:D11,3)+SMALL(D2:D11,4)+SMALL(D2:D11,5)

also if I have a # like 22.856489 how do I round down to just 2 decimal points like 22.86?
DPR250R wrote:
Are you sure you are getting all the data in there? When selecting your source data... are you selecting entire columns? If you add data after...
Are you sure you are getting all the data in there?

When selecting your source data... are you selecting entire columns?

If you add data after creating the table you need to possibly re-size and refresh your source.

Also... how are you entering data? Typing straight into the cells?Are you using any type of data validation or forms? Its important that your dates, numbers are being entered as the correct data types. True dates and true numbers.

or are you pulling from some type of source? Accointing program etc...

Typing from phone... Sry for typos
all that I am doing is marking it down, no decimals possibly dates with single digits vs double ?
6/01/2016 vs 6/1/2016 ? it was 16 rows nothing fancy or imported from other file

DATE Vendor AMOUNT
6/2/2016 ABC 486
reded
Posts
3685
Joined
3/26/2011
Location
KS US
7/1/2016 4:14pm
Sheesh! I use Excel everyday but some of you mfers are in a whole 'nother league.

Tip of the cap guys.
DPR250R
Posts
2128
Joined
9/14/2006
Location
NJ US
7/1/2016 5:13pm
500guy wrote:
all that I am doing is marking it down, no decimals possibly dates with single digits vs double ? 6/01/2016 vs 6/1/2016 ? it was 16...
all that I am doing is marking it down, no decimals possibly dates with single digits vs double ?
6/01/2016 vs 6/1/2016 ? it was 16 rows nothing fancy or imported from other file

DATE Vendor AMOUNT
6/2/2016 ABC 486
Hmmm...

What part is not calculating correctly? Total by vendor?

On the pivot... Right click the metric and check "summarize field values as" do you have it set to "Sum", "Count", "Average"? Is that what you want?

Maybe post screen shot of raw data and pivot. Of course change vendor names if business sensitive.
500guy
Posts
12478
Joined
8/15/2006
Location
AZ US
7/1/2016 7:28pm
500guy wrote:
all that I am doing is marking it down, no decimals possibly dates with single digits vs double ? 6/01/2016 vs 6/1/2016 ? it was 16...
all that I am doing is marking it down, no decimals possibly dates with single digits vs double ?
6/01/2016 vs 6/1/2016 ? it was 16 rows nothing fancy or imported from other file

DATE Vendor AMOUNT
6/2/2016 ABC 486
DPR250R wrote:
Hmmm... What part is not calculating correctly? Total by vendor? On the pivot... Right click the metric and check "summarize field values as" do you have...
Hmmm...

What part is not calculating correctly? Total by vendor?

On the pivot... Right click the metric and check "summarize field values as" do you have it set to "Sum", "Count", "Average"? Is that what you want?

Maybe post screen shot of raw data and pivot. Of course change vendor names if business sensitive.
So I calculated all the totals and vendors put them back in just one value for each, ran the pivot table again and it worked fine.

I'm sure it has to do with formatting or the data entry.

it was doing things like the total when I would highlight the values 10889 then when pivot tabled the total would real 1108 and the vendor numbers were wrong, some to high some to low.

I forgot to bring it home will revisit it next week.

Thanks for the help.

Post a reply to: Anyone here know excel?

The Latest