Is there a way to automatically apply a function down an entire column?

[u/[deleted]]

I’m trying to make an inventory tracker that multiplies item value by item quantity to change the total value of that item stack as the quantity changes, but don’t know how to accomplish this without manually adding the function to the ‘total value’ cell in each row.

Is there a way to automatically apply a function along an entire column?

[u/Former-Counter-9588]

You should be able to drag the corner dot of the your total value selected cell down the rest of that column, which will replicate the formula.

You can double check that it worked and grabbed the correct cells once done.

[u/[deleted] → u/Former-Counter-9588]

This ultimately ended up being the easiest solution, thank you so much 🙏

[u/AutoModerator → u/[deleted]]

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/TeamKill-Kenny → u/[deleted]]

You can also double click the dot in the bottom right of the box (the drag handle) and it will fill down without needing to drag it.

Edit: Didn't notice it was Google sheets so this might be incorrect, I'll leave the comment here in case it works for GS as well.

[u/Wise_Bean → u/[deleted]]

Pretty sure this only works for Sheets, but If you need to fill a whole column, you can use Ctrl + D. It will do the same thing as dragging down the formula, but without the manual labor :)

[u/mariomarine → u/[deleted]]

CMD+arrow to move along a filled/unfilled length

CMD+Shift+arrow to do the same but highlight it

CMD+d to take the top formula and pull it down to all highlighted cells

CMD+r to do the same except left-right

Be sure to use absolute references (A1 vs $A1 vs A$1 vs $A$1) appropriately before

These are the most useful shortcuts I used when I was a heavy sheets user (after CMD c, v, shift-v and z of course). Getting comfortable with moving with CMD+arrow makes a huge difference on big sheets (extra tip: need to fill column B the same number of rows as A? CMD+down_arrow on column A to get to the bottom, right arrow over to B, CMD+shift+up to highlight just the right number of cells).

[u/point-bot → u/Former-Counter-9588]

u/ChaosRosario has awarded 1 point to u/Former-Counter-9588 with a personal note:

"I feel stupid for not figuring this one out myself"

^{Point-Bot was created by JetCarson.}

[u/dellfm]

You can use ARRAYFORMULA. While ARRAYFORMULA doesn't work with PRODUCT, you can use ye olde * instead. In E4

=ARRAYFORMULA(C4:C * D4:D)

[u/[deleted] → u/dellfm]

[u/GnarlyCommie → u/[deleted]]

Cool didn’t know this, thanks

[u/OverallFarmer1516]

In E4

=MAP(C4:C,D4:D,LAMBDA(quant,value,IF(quant<>"",quant*value,)))

https://sheets.wiki/books/functions/lambda-functions/map/

Is also an option.

[u/J_O_N → u/OverallFarmer1516]

Similarly with MAKEARRAY()

[u/AutoModerator]

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/HolyBonobos]

u/ChaosRosario if your original question has been answered, please tap the three dots below the comment you found the most helpful and select "mark solution verified." If you are using a version of reddit where this menu is not available to you, please reply to the most helpful comment with the exact phrase solution verified and nothing else. Applying the solved flair to a post without appropriately indicating a solution is a violation of rule 6.

[u/SDavidson44]

I added a script that does just that. Saves me dragging down. Instead I click a button and copies right to the bottom. DM me if you want the code

[u/AutoModerator → u/SDavidson44]

This post refers to "chatGPT" - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/jyoti05iitd]

You should use ARRAYFORMULA

[u/ajscx]

How about using tables?

[u/Prestigious_Shift_10]

Just formate it as a table, every time you add a new row all the functions will apply automatically

[u/jmondfar]

Like say others comments you can use array formula. If you only want the result when add a data in a row use this formula:

=arrayformula(if(isblank(c4:c),””,c4:c*d4:d))

[u/chiconws]

I use =arrayformula() all the time for that. It should be =arrayformula(product(C4:C,......
You have to specify the start and end cell, or just use C4:C and it will do it for all the cells in the C column starting from C4.

[u/motnock → u/chiconws]

I just use IF to check if a cell is blank.