Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 397K Members
- 3,667 Online
- 432K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Divide a quantity as whole number among multiple cells

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## Divide a quantity as whole number among multiple cells

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 07:06 PM

Hello,

Any suggestion for an excel formula to derive A2, B2, C2, D2 values.

Eg:

Category | Income | A | B | C | D |

Sal1 | 11 | 3 | 3 | 3 | 2 |

if we divide 11 among 4 columns, 2.75 will result in the 4 cells

But I need the excel formula to get the whole number, next integer(3 for 2.75) in all cells.

Any response will be helpful. Thanks

Labels:

15 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 08:36 PM - edited 02-11-2018 04:29 PM

This is what I guess you want

=ROUNDUP(IF(B2=$B2,$B2,$B2-SUM($C2:OFFSET(C2,0,-1)))/(SUMPRODUCT(NOT($1:$1=0)*1)-COLUMN(B2)),0)

Assume that you enter the formula at C2, and copy & paste the formula to the cells you need it. If later on, you want E, F, G etc., you just need to add the headers and paste the formula.

Eg:

Category | Income | A | B | C | D |

something | 6 | 2 | 2 | 1 | 1 |

another thing | 5 | 2 | 1 | 1 | 1 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 09:05 PM

Remya,

your cell references (A2, B2, ...) can't be correct.

I assume "Category" is in A1 and "2" is in F2.

=IF(COLUMN()=6,$B2-SUM($C2:$E2),ROUNDUP($B2/4,0))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2018 08:29 AM

Thank you Willy for the response, Sorry if I didn't articulate my problem clearly;

The cells in which I require values are C2,D2,E2,F2

its not required to have values in all cells, if we take 5 as example:

C2 has to take 2

D2 has to take 2

E2 has to take 1

D2 can be blank

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2018 08:33 AM

Hello Detlef,

Sorry I didn't explain it right!

I look for auto population of values in C2,D2,E2 and F2 cells.

and if we the quantity to divide is 5,

I need to get

C2=2

D2=2

E2=1

F2=0(blank)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2018 09:15 AM

Solution=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))

Best Response confirmed by
Remya Ramakrishnan (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2018 10:16 AM

Thank you for the response, I am unable to get the expected results

When I am pasting the formula in C2, it results in circulate reference

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2018 04:45 PM

It is because your sheet keeps my previous version formula. You may clear C2, D2, E2 and F2. Then, paste the latest formula to C2.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2018 08:41 AM

hmm..Thank you Willy for the support

its solved my issue

Thank you!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-20-2018 03:40 PM

I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable.

Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

The formula should be variable to be copied across 2 cells or 20.

Thoughts?

Allain

allainm@gmail.com

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-20-2018 03:42 PM

I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable.

Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

The formula should be variable to be copied across 2 cells or 20.

Thoughts?

Allain

allainm@gmail.com

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-25-2018 09:57 AM

I am so sorry for the late reply. I am not sure if the following approach suits your needs.

The Formula is

=$A$1/SUMPRODUCT(--(ISFORMULA($B$1:$F$1)))

A | B | C | D | E | F | |

1 | 50 | =TheFormula | =TheFormula | =TheFormula | =TheFormula | =TheFormula |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-29-2018 09:25 PM

Thanks for trying, I couldn't get that formula to work at all.

:-(

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-07-2018 10:10 PM

May I have your excel version? Does it have IsFormula Function?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-07-2018 11:21 PM

I have 2016, I don't know what isFormula function is.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-07-2018 11:57 PM

Reference : IsFormula Function

Related Conversations

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
99K
Views

5 Likes

28 Replies

*Updated 9/3* Syncing in Microsoft Edge Preview Channels

Elliot Kirk
in
Articles
on
05-01-2019
35.8K
Views

23 Likes

202 Replies

Early preview of Microsoft Edge group policies

Sean Lyndersay
in
Discussions
on
06-14-2019
18.6K
Views

20 Likes

65 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
12.5K
Views

0 Likes

5 Replies

$cred = Get-Credential without asking for prompts in powershell

TejCGS
in
Office 365
on
04-25-2019
21.6K
Views

0 Likes

6 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft