Excel Formula Help

Copper Contributor

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?

 

Excel Example.jpg

9 Replies

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

Riny_van_Eekelen_0-1642580709266.png

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.

See also attached file.

 

@Riny_van_Eekelen 

SUMPRODUCT() shall work without CSE

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.
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:-)

@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.

@GeoffT2022 

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.

@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:

Screenshot 2022-01-19 at 16.54.24.png

 

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.

@GeoffT2022 

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