the only possible way would be:
=ARRAYFORMULA(IFERROR(ROUNDUP(
QUERY(QUERY(Data!A3:D,
"SELECT A,sum(B),C,D where A is not null GROUP BY A,C,D", 0),"OFFSET 1",0)),
QUERY(QUERY(Data!A3:D,
"SELECT A,sum(B),C,D where A is not null GROUP BY A,C,D", 0),"OFFSET 1",0)))
Google Sheets formula to round up columns to nearest thousand and sum - Web Applications Stack Exchange
How to sum a column and round up at the same time
Using SUM and MROUND in same cell?
Google Sheets - How to round SUMs across several cells once a number limit is reached - Stack Overflow
Is there a way to round up text-based data in Google Sheets?
Can I round up only positive numbers in Google Sheets?
How do I round up to the nearest 5 or 10 in Google Sheets?
Videos
Trying to to
=Sum(A1:A10) but also have it round up to the second decimal place.
What is the equation for this?
I have tried variations but keep getting a value! Error afterwards. I could just link the cell and round up the number but am trying to learn more functions.
Thank you
I'm having some trouble with putting together a spreadsheet for weightlifting. Basically I want to use the SUM function =SUM(B6*0.9)+(D6*C1) to do some multiplication and addition. Once the sum is calculated I would like the sum to be rounded to the nearest 0 or 5. So if the number is 123 I'd like it to be 125 or if the number is 91 I'd like it to be 90. Kind of silly but I think the overall look of the spreadsheet would be cleaner.
Why not use one number instead and calculate all these currencies from it?
For instance you can have number 4,202,754 and it will populate copper with 754, silver with 202, gold with 4 and gems with 0.
For instance, we will store this number in B2. For copper we'll use C2 field with this value: =MOD(B2, 1000) For silver we'll use D2 field with this value: =MOD(FLOOR(B2 / 1000, 1), 1000) For gold we'll use D2 field with this value: =MOD(FLOOR(B2 / 1000000, 1), 1000) For gems we'll use D2 field with this value: =FLOOR(B2 / 1000000000, 1)
={SUM(E3:E)+
QUOTIENT(SUM(F3:F), 100)+
QUOTIENT(MOD(SUM(F3:F), 100)+
QUOTIENT(SUM(G3:G), 1000)+
QUOTIENT(MOD(SUM(G3:G), 1000)+
QUOTIENT(SUM(H3:H), 1000),1000),100),
MOD(MOD(SUM(F3:F), 100)+
QUOTIENT(SUM(G3:G), 1000)+
QUOTIENT(MOD(SUM(G3:G), 1000)+
QUOTIENT(SUM(H3:H), 1000),1000),100),
MOD(MOD(SUM(G3:G), 1000)+
QUOTIENT(SUM(H3:H), 1000),1000),
MOD(SUM(H3:H), 1000)}
Big time novice when it comes to building spreadsheets in need of help!
Formula I am working with: =If(A2+B2+C2=0, “”, Roundup(Sum(B2:D2)/Vlookup($O2,Pricing!$A$2:$F$10,6,False)))
In this case A,B and C are sq ft numbers and cell “O2” is a drop down list that pulls the coverage rate for the selected product from the drop down options to give me a rounded up amount of product needed. I have anywhere from 10 to 50 items in these drop downs to choose from.
I am plugging in a “N/A” option to the drop down lists now and I would like to keep this one “N/A” option to only pull the exact number and not round it up. Any advise??
Again, I am new to these formulas so please let me know if there’s more information needed to help me out!
TIA!!
As the title says, I'm trying to subtract the value of one cell from the value of another cell, divide it by a specific number, then round up to the nearest whole number. I've tried different formulas but always end with an error. I've tried the & between formulas, embedding the first formula in parentheses of the last one, but nothing is working.
In the shot below, I need the Find row to have those decimals rounded up:
-
Count shows how many of each color there is
-
Shards shows how much worth the amount of each color has added together (blue = 1, yellow = 2, purple = 5)
-
Total is all the values in Shards combines
-
Need is the target of a value >= of what the Total is
So B6 is simple, I can just subtract the Total from the Need and be done, but dividing by 2 and 5 for C6 and D6 will often give me remainder decimals that I want removed. How do I go about setting up these formulas?
Let's suppose your numbers list begins in A2 and runs downward (i.e., A2:A). You can use this:
=SUM(FILTER(A2:A,A2:A=INT(A2:A)))
In plain English, this reads as follows: "Sum only those numbers in A2:A where the original value is the same as the integer-only portion of that value."
Try:
=sumproduct((A1:A5)*(A1:A5=int(A1:A5)))
this will also work in Excel
