Division to exclude input value

[Huizar]

Hi Excel Fans,

I have been racking my brain probably for an easy formula, which I can't get to work. I'm looking for a formula that will give me the percent for each item based on Totals, the caveat is let's say someone manually makes an adjustment to the value column, i want to now only account for the cells that have formulas only. Hence my new Output column. Anyone that could help would be greatly appreciated.

Value	Regular Output %	New Output	Notes
40	26.7%	0%	40 is a manual Input
50	33.3%	45.5%	50 is formula
60	40%	54.5%	60 is a formula
Total 150	100%	100%

 

[StephenCrump]

It's not clear what your results are meant to be?

But you can use =ISFORMULA(reference) to determine whether a cell has a formula or a value, and define your formula appropriately:

	A	B
1	100	50
2	80	40
3	60	60 is a manual input
4
Sheet1

Cell Formulas
Range	Formula
A1	A1	=100
A2	A2	=80
B1:B3	B1	=IF(ISFORMULA(A1),50%*A1,A1 & " is a manual input")

 

[Huizar]

It's not clear what your results are meant to be?

But you can use =ISFORMULA(reference) to determine whether a cell has a formula or a value, and define your formula appropriately:

	A	B
1	100	50
2	80	40
3	60	60 is a manual input
4
Sheet1

Cell Formulas
Range	Formula
A1	A1	=100
A2	A2	=80
B1:B3	B1	=IF(ISFORMULA(A1),50%*A1,A1 & " is a manual input")

Hi,

Sorry for not being clear. What i'm trying to do is get the percent for each line item of the total. So Let's say someone types in the 40 hours with their keyboard, i want to ignore the 40 and allocate the new amounts based on Leaks and Drilling because they are formula driven. I put the note column to kind of give an idea of what i want to do. I want the New Percent of total output. I put to get a better idea. I really appreciate your help on this.

Thanks.

Items	Hours	Percent of Total	New Percent of Total	Note
Observations	40	23.5%	0%	Since 40 Hours is manual input by someone, i want to ignore it as part of the total.
Leaks	50	29.4%	38%	New Distribution of the 50/(50+80)
Drilling	80	47.1%	62%	New Distribution of the 80/(50+80)
Total	170	100.0%	100%

 

[StephenCrump]

Sorry for not being clear ...

Much clearer now, thanks.

Something like this perhaps:

	A	B	C	D
1	Items	Hours	Percent of Total	Revised %
2	Observations	40	23.53%	Nil - ignore manual entry
3	Leaks	50	29.41%	38.46%
4	Drilling	80	47.06%	61.54%
5	Total	170	100.00%	100.00%
6	Revised total	130
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=B2/B$5
D2:D4	D2	=IFERROR(IF(ISFORMULA(B2),B2/B$6,"Nil - ignore manual entry"),"-")
C5:D5	C5	=SUM(C2:C4)
B3	B3	=25+25
B4	B4	=2*40
B6	B6	=SUMPRODUCT(IF(ISFORMULA(B$2:B$4),B$2:B$4))