# Excel Formula Help

Occasional Contributor

# Excel Formula Help

I'm running Windows 10, Excel 2013

In the examples below, I'd like to simply enter the count in C1 and get the result in D5. What would the formulas be in C2, C3, and C4 to break down the number in C1?

9 Replies

# Re: Excel Formula Help

@GeoffT2022 You could use the method demonstrated in the picture below:

Not sure how this will behave in Excel2013. YOu probably need to confirm the formula with ctrl-shift-enter (CSE) which will put curly brackets around it all.

# Re: Excel Formula Help

SUMPRODUCT() shall work without CSE

# Re: Excel Formula Help

Thank you for your help! However, I do not quite understand. I am looking for the formulas that would go in C2, C3, and C4 according to the quantities shown in column A. I'm not sure if I'm using the proper vernacular but C2:C4 parses the number inserted in C1 according to the maximum values inserted in A2:A4.

NOTE: I did not use formulas in my examples.

# Re: Excel Formula Help

Thank you for your help! However, I do not quite understand. I am looking for the formulas that would go in C2, C3, and C4 according to the quantities shown in column A. I'm not sure if I'm using the proper vernacular but C2:C4 parses the number inserted in C1 according to the maximum values inserted in A2:A4.

NOTE: I did not use formulas in my examples.

BTW- I'm new to the forum and not a mathematician:-)

# Re: Excel Formula Help

@GeoffT2022 Then I misunderstood. My thinking was to avoid the row-by-row calculation and come up with a single formula that calculates the total amount payable in one go.

# Re: Excel Formula Help

The question is it acceptable to split Qty column on two with From/To values, or you prefer to work with texts as it is now? The latest bit complicates the formulas which will be harder to maintain.

# Re: Excel Formula Help

@GeoffT2022 Assuming that the boundaries for each group are fixed, you could just use three formulae C2, C3 and C4 as demonstrated in the picture below:

# Re: Excel Formula Help

Thank you for the MIN Function. Unfortunately if the value in C1 is less than 100 then that array of formulae wouldn't work the way I needed. Fortunately, the formula in C3 and C4 fixes my problem when I use =IF(C1>100,100,C1) in the C2 cell.

# Re: Excel Formula Help

Indeed. I forgot about that. Good you figured it out yourself.