number of times

P

prasad

Please give me answer for following question.

I have entered value 6650 value in this cell, now i want to know how much
100 in this cell and how much 50 in this cess.
 
N

Nick Hodge

Prasad

If I understand your question then use two other cells in one type (Presume
the number 6650 is in A1, so change as necessary)

=A1/100

In the other type

=A1/50

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
P

prasad

I have entered A1 Cell= 6500

1) 100 50 20 10 5 2 1
2) 65 1 1 1 1
3) 6500 50 0 10 5 0 1

Please see the above table I have entered a) Rupees Value b) I want Answer
like that c) actual calculation of cell

My question is I want to know that how much times I have entered e.g.
100,50,20,10,…… how much times in the value 6566. Answer is 65,1,1,......
I want to function for that.
 
N

Nick Hodge

Prasad

Hope I understand now. I am seeing a grid with the number in A2, Titles in
B1:H1 as follows: 100, 50, 20, 10, 5, 2, 1). The following formula in cells
B2:H2 respectively

=INT(A2/$B$1)
=INT((A2-(B2*$B$1))/$C$1)
=INT((A2-((B2*$B$1)+(C2*$C$1)))/$D$1)
=INT((A2-((B2*$B$1)+(C2*$C$1)+(D2*$D$1)))/$E$1)
=INT((A2-((B2*$B$1)+(C2*$C$1)+(D2*$D$1)+(E2*$E$1)))/$F$1)
=INT((A2-((B2*$B$1)+(C2*$C$1)+(D2*$D$1)+(E2*$E$1)+(F2*F1)))/$G$1)
=INT((A2-((B2*$B$1)+(C2*$C$1)+(D2*$D$1)+(E2*$E$1)+(F2*$F$1)+(G2*$G$1)))/$H$1)

There will undoubtedly be a more elegant array solution, but this appears to
work

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
R

Ron Rosenfeld

I have entered A1 Cell= 6500

1) 100 50 20 10 5 2 1
2) 65 1 1 1 1
3) 6500 50 0 10 5 0 1

Please see the above table I have entered a) Rupees Value b) I want Answer
like that c) actual calculation of cell

My question is I want to know that how much times I have entered e.g.
100,50,20,10,…… how much times in the value 6566. Answer is 65,1,1,......
I want to function for that.
Click to expand...

Set up a grid in B1:H1 with:

B1: 100
C1: 50
D1: 20
E1: 10
F1: 5
G1: 2
H1: 1

Put your number in A2. Then

B2: =INT($A2/B$1)
C2: =INT(($A2-SUMPRODUCT($B$1:B$1,$B2:B2))/C$1)

Select C2 and copy/drag the equation across to H2.

You can then select C2:H2 and copy/drag down as far as needed for different
entries in column A. The formulas should adjust appropriately as you do this.


--ron
 
R

Richard Buttrey

I have entered A1 Cell= 6500

1) 100 50 20 10 5 2 1
2) 65 1 1 1 1
3) 6500 50 0 10 5 0 1

Please see the above table I have entered a) Rupees Value b) I want Answer
like that c) actual calculation of cell

My question is I want to know that how much times I have entered e.g.
100,50,20,10,…… how much times in the value 6566. Answer is 65,1,1,......
I want to function for that.
Click to expand...

Can you describe what you are trying to achieve a little more clearly
please?

It seems that you're trying to create some function which relates the
numbers 100, 50, 20 etc., with the number in A1, and produce the
result 65, 1, ?,?,?,?,? and 6500, 50, 0, 10, 5,0,1

However it's not clear to me how these are related and why for
instance you are only showing five results in 2)

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
N

Nick Hodge

Richard

I've had no response from the poster yet but I suspected he wanted to know
how many 100's, how many 50's, how many 20's, etc were in the number in A1,
so in the example given (6566) (I think he has confused his examples), you
end up with 65x100s, 1x50s, 0x20's, 1x10s, 1x5s, 0x2s and 1x1s. (I think he
just left gaps instead of zeroes)

I suspect we'll know when time zones allow ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
R

Richard Buttrey

Richard

I've had no response from the poster yet but I suspected he wanted to know
how many 100's, how many 50's, how many 20's, etc were in the number in A1,
so in the example given (6566) (I think he has confused his examples), you
end up with 65x100s, 1x50s, 0x20's, 1x10s, 1x5s, 0x2s and 1x1s. (I think he
just left gaps instead of zeroes)

I suspect we'll know when time zones allow ;-)
Click to expand...

You're probably right Nick. One of my guesses was that he was making
up wage packets and wanting to know how many notes/coins of each
denomination was needed.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top